管理Oracle約束與分區(qū)表
1．約束
作用：
約束用于確保數(shù)據(jù)庫數(shù)據(jù)的完整性,在oracle數(shù)據(jù)庫中,可以使用約束,觸發(fā)器和應(yīng)用代碼(過程,函數(shù))3種方法實現(xiàn)數(shù)據(jù)完整性,這3種方法中,因為約束易于維護(hù),并且具有***的性能,所以實現(xiàn)數(shù)據(jù)完整性***約束.
分類：
約束的種類有：not null,unique,primary key,foreign key,check
Not null確保字段值不能為空
Unique:確保字段值唯一性
Primary key,最常用的約束（主鍵約束），主鍵約束的列值不僅不能重復(fù)，也不能為NULL，注意一張表最多只能有一個主鍵約束，當(dāng)定義主鍵約束后oracle自動建立一個以主鍵為關(guān)鍵字段的索引。
Foreign key:定義了主從表之間的關(guān)系，foreign要定義在從表上，但主表必須具有主鍵約束或唯一約束，當(dāng)定義froeign key后外部鍵列的數(shù)據(jù)必須在主表的主鍵列中存在，或者為NULL
Check::用于強(qiáng)制表行數(shù)據(jù)必須滿足的條件，如工資表，工人工資必須在2000-5000之間
約束狀態(tài)
enable validate:是默認(rèn),新舊數(shù)據(jù)同時滿足約束規(guī)則
enable novalidate:舊數(shù)據(jù)可以不滿足,檢新插入的數(shù)據(jù)要滿足約束
disable validate:不允許在表上執(zhí)行任何DML操作,主要用在分區(qū)表,對于主鍵和唯一約事,會刪除相應(yīng)的唯一索引,但約束狀態(tài)任可用
disable novalidate數(shù)據(jù)可不滿足約束規(guī)則,對于主鍵和唯一約事,會刪除相應(yīng)的唯一索引,
約束常用語句
- create table t(i number,v mubmer not null)
- create table t(i number,v mubmer unique)
- create table t(i number constraint pk_i primary key,v number)
- create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v));
- alter table t add constraint pk_i primary key (i)
- alter table t modify i not null;
- alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];
- alter table t add constraint t_i check(i in (1,2,3,4,5));
- alter table t disable novalidate constraint i
- alter table t enable novalidate constraint check_i
- alter table t drop constraint i;
- alter table t drop primary key i;
#常用的數(shù)據(jù)字典
- dba_constraints
- dba_cons_columns
- user_cons_columns
- user_constraints
簡單應(yīng)用
檢驗當(dāng)為一個表建立主鍵索時后，這個字段是否滿足約束非空，唯一性，而且自動建立一個索引，并查看當(dāng)把約束狀態(tài)關(guān)閉再次插入相同的記錄,是否還能把把約束設(shè)為enable ividate狀態(tài)。
- SQL> create table t(i number constraint pk_i primary key,v number);
- SQL> insert into t values(1,2);
- SQL> insert into t values(3,4);
- SQL> commit;
- SQL> select * from t;
- I V
- ---------- ---------------------------
- 1 2
- 3 4
現(xiàn)在表中有兩條記錄，然后給它插主鍵為空或相同的值
- SQL> insert into t values('',10);
- ERROR at line 1:
- ORA-01400: cannot insert NULL into ("Y"."T"."I")
- SQL> insert into t values(1,10);
- ERROR at line 1:
- ORA-00001: unique constraint (Y.PK_I) violated
可以看到全部報錯，此時主鍵不能為空或重復(fù)
查看是否建立索引
- SQL> select index_name from user_indexes;
- INDEX_NAME
- ------------------------------
- PK_I
把約束關(guān)閉再次做同樣的操用
- SQL> alter table t disable novalidate constraint pk_i;
- Table altered.
- SQL> insert into t values('',10);
- 1 row created.
- SQL> insert into t values(1,10);
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from t;
- I V
- ---------- ----------
- 1 2
- 3 4
- 10
- 1 10
- SQL> select index_name from user_indexes;
- no rows selected
可見當(dāng)把約束關(guān)閉后就可以何意給表插數(shù)據(jù)了，而具索引也自動刪除了。
現(xiàn)在激活約束
- SQL> alter table t enable validate constraint pk_i;
- alter table t enable validate constraint pk_i
- ERROR at line 1:
- ORA-02437: cannot validate (SYS.PK_I) - primary key violated
因為表中主鍵有相同的值所以不能恢復(fù)到enable validate狀態(tài)了
再次測試回復(fù)到enable novalidate
- SQL> alter table t enable novalidate constraint pk_i;
- alter table t enable validate constraint pk_i
- ERROR at line 1:
- ORA-02437: cannot validate (SYS.PK_I) - primary key violated
也失敗了,
因為表中主鍵有了空值和相同的值,所以恢復(fù)不到enable validate狀態(tài),但enable novalidate不檢查舊數(shù)據(jù)所以應(yīng)該還能恢復(fù)到enable novalidate.
要想恢復(fù)到enable novalidate必須建立主鍵索引(關(guān)閉約束時自動刪除的那個索引)如下:
- SQL> create index pk_i on t(i);
- Index created.
然后恢復(fù)到enable disvalidate,以后再插數(shù)據(jù)不能為空,主鍵也不能重復(fù)了.
- SQL> alter table t enable novalidate constraint pk_i;
- Table altered.
- SQL> insert into t values(1,14);
- insert into t values(1,14)
- ERROR at line 1:
- ORA-00001: unique constraint (SYS.PK_I) violated
2.修正約束數(shù)據(jù)
當(dāng)給一個表作主鍵約束時,因為已存數(shù)據(jù)不滿足約束規(guī)則,會提示錯誤信息,些時必須對數(shù)據(jù)進(jìn)行修正
要修正數(shù)據(jù)先找出不滿足約束的數(shù)據(jù)
如下表,有不滿足約束的數(shù)據(jù)
- SQL> select * from t;
- I V
- ---------- ------------------------
- 1 2
- 3 4
- 15 12
- 15 10
如果一個表數(shù)據(jù)量多可通過如下方法查找
- SQL> alter table t drop constraint pk_i;
- Table altered.
- SQL>conn y / 123
- SQL> @$ORACLE_HOME/rdbms/admin/utlexcpt.sql
- Table created.
- SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;
- select * from t where rowid in (select row_id from exceptions)
- I V
- ---------- ------------------------
- 15 12
- 15 10
找到了重復(fù)的記錄
修正
- SQL>update t set i=10 where v=12;
- SQL> select * from t;
- I V
- ---------- ----------
- 1 2
- 3 4
- 10 12
- 15 10
再建主鍵約束
- alter table t add constraint pk_i primary key (i)
- Table altered.
成功了!!!
#p#
二:分區(qū)表管理
作用:將在張大表的數(shù)據(jù)分布到多個表分區(qū)段,不同分區(qū)彼此獨(dú)立,從而提高了表的可用性和性能
種類:范圍分區(qū),散列分區(qū)(使用HASH算法,最常使用),列表分區(qū),范圍/散列組合分區(qū),范圍/列表組合分區(qū)
范圍分區(qū)表
創(chuàng)建范圍分區(qū)表
- create table t(v number,b number)
- partition by range(v) (
- partition p1 values less than ('11') tablespace test1,
- partition p2 values less than ('21') tablespace test2);
增加與刪除分區(qū)
#增加分區(qū)
- alter table t add partition p3 values less than ('31') tablespace test3;
- alter table t drop partition p3
一個時間分區(qū)的例子
- alter session set nls_data_lanage=AMERICAN;
- alter session set nls_data_format='DD-MON-YYYY'
- create table t(v_date date,b number)
- partition by range(v_date)(
- partition p1 values less than ('01-APR-2009') tablespace test1,
- partition p2 values less than ('01-JUN-2009') tablespace test2);
2.散列分區(qū)表(最常用)
創(chuàng)建
- create table t1(
- v number,b number)
- partition by hash(v)
- (partition p1 tablespace test1,
- partition p2 tablespace test2);
增加分區(qū)
- alter table t add partition p3 tablespace test3;
刪除分區(qū)
- alter table t drop coalesce partition;
3.列表分區(qū)
建列表分區(qū)
- create table t(
- v varchar2(10),
- b number
- )partition by list(v)
- (partition p1 values('a','b') tablespace test1,
- partition p2 values('c','d') tablespace test2);
#插入數(shù)據(jù)
- SQL> insert into t values('a',10);
- SQL> insert into t values('d',20);
#注意,插入數(shù)據(jù)時***個字段只能為a,b,c,d
- SQL> insert into t values('f',30);
- ERROR at line 1:
- ORA-14400: inserted partition key does not map to any partition
#查詢
- select * from t;
- select * from t partition(p1);
- select * from t partition(p2);
- select * from t where v=XXX
增加分區(qū)
- alter table t add partition p3 values('31','32') tablespace test3;
刪除分區(qū)
- alter table t drop partition p3
4.范圍/散列組合分區(qū)
建立散列組合分區(qū)
- create table t(
- v number,b number)
- partition by range(v)
- subpartition by hash(b) subpartitions 2
- store in (test1,test2)(
- partition p1 values less than ('11'),
- partition p2 values less than ('21'));
查詢
- select * from t;
- select * from t partition(p1);
- select * from t where ....
增加主分區(qū)和子分區(qū)
- alter table t add partition p3 values less than ('31') tablespace test3;
- alter table t modify partition p3 add subpartition;
刪除分區(qū)
- alter table t coalesce partition;
- alter table t modify partition p1 coalesce subpartition;
5.范圍/列表組合分區(qū)
創(chuàng)建
- create table t(
- v number,b number)
- partition by range(v)
- subpartition by list(b)
- (partition p1 values less than ('11') tablespace test1(
- subpartition p1_1 values('1','3'),
- subpartition p1_2 values('5','6')
- ),
- partition p2 values less than ('21') tablespace test2(
- subpartition p2_1 values('13','14'),
- subpartition p2_2 values('15','16')
- ));
查詢
- select * from t
- select * from t partition(p1)
- select * from t subpartition(p1_1)
- select * from t where .....
- select segment_name,partition_name,tablespace_name
- from user_segments where segment_name='T';
增加分區(qū)和子分區(qū)
- alter table t add partition p3 values less than ('31') tablespace test3(
- subpartition p3_1 values('25','26'),
- subpartition p3_2 values('22','23'));
- alter table t modify partition r3
- add subpartition r3_3 tablespace test3 values('28','29');
刪除分區(qū)
- alter table t modify partition p1 coalesce subpartition;
其它設(shè)置
- 交換分區(qū)數(shù)據(jù)
- alter table t exchange partition p1 with table tt;
- 載斷分區(qū)
- alter table t truncate partition p1;
- 修改分區(qū)名
- alter table t rename partition p2_1 to p2;
- 合并分區(qū)
- alter table t merge partitions p1,p2 into partition p01
- 重組分區(qū)
- alter table t move partition p1 tablespace test04
- 為列表分區(qū)和子分區(qū)加值
- alter table t modify partition p1 add values('111');
- alter table t modify subpartition p3_1 add values('111');
- 從列表分區(qū)和子分區(qū)中刪除值
- alter table t modify partition p1 drop values('111')
- alter table t modify subpartition p3_1 drop values('111')
分區(qū)表常用的數(shù)據(jù)字典
- 分區(qū)表信息: dba_part_tables
- 顯示分區(qū): dba_tab_partitions
- 顯示子分區(qū): dba_tab_subpartitions
- 顯示分區(qū)列: dba_part_key_columns
- 顯示子分區(qū)列:dba_subpart_dey_columns
- 顯示分區(qū)索引:dba_part_indexes
- 顯示索引分區(qū):dba_ind_partitions
來源：本文出自 “追求” 博客。
【編輯推薦】
