程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 管理Oracle約束與分區表

管理Oracle約束與分區表

編輯:Oracle數據庫基礎

1.約束

作用:

約束用於確保數據庫數據的完整性,在Oracle數據庫中,可以使用約束,觸發器和應用代碼(過程,函數)3種方法實現數據完整性,這3種方法中,因為約束易於維護,並且具有最好的性能,所以實現數據完整性首選約束.

分類:

約束的種類有:not null,unique,primary key,foreign key,check

Not null確保字段值不能為空

Unique:確保字段值唯一性

Primary key,最常用的約束(主鍵約束),主鍵約束的列值不僅不能重復,也不能為NULL,注意一張表最多只能有一個主鍵約束,當定義主鍵約束後Oracle自動建立一個以主鍵為關鍵字段的索引。

Foreign key:定義了主從表之間的關系,foreign要定義在從表上,但主表必須具有主鍵約束或唯一約束,當定義froeign key後外部鍵列的數據必須在主表的主鍵列中存在,或者為NULL

Check::用於強制表行數據必須滿足的條件,如工資表,工人工資必須在2000-5000之間

約束狀態

enable validate:是默認,新舊數據同時滿足約束規則

enable novalidate:舊數據可以不滿足,檢新插入的數據要滿足約束

disable validate:不允許在表上執行任何DML操作,主要用在分區表,對於主鍵和唯一約事,會刪除相應的唯一索引,但約束狀態任可用

disable novalidate數據可不滿足約束規則,對於主鍵和唯一約事,會刪除相應的唯一索引,

約束常用語句

  1. create table t(i number,v mubmer not null)  
  2. create table t(i number,v mubmer unique)  
  3. create table t(i number constraint pk_i primary key,v number)  
  4. create table t2(c number,d number,constraint fk_d foreign key(c),references t1(v));  
  5. alter table t add constraint pk_i primary key (i)  
  6. alter table t modify i not null;  
  7. alter table t add constraint t_i unique(i)[(create index ind_name on t(i))];  
  8. alter table t add constraint t_i check(i in (1,2,3,4,5));  
  9. alter table t disable novalidate constraint i  
  10. alter table t enable novalidate constraint check_i  
  11. alter table t drop constraint i;  
  12. alter table t drop primary key i; 

#常用的數據字典

  1. dba_constraints  
  2. dba_cons_columns  
  3. user_cons_columns  
  4. user_constraints  

簡單應用

檢驗當為一個表建立主鍵索時後,這個字段是否滿足約束非空,唯一性,而且自動建立一個索引,並查看當把約束狀態關閉再次插入相同的記錄,是否還能把把約束設為enable ividate狀態。

  1. SQL> create table t(i number constraint pk_i primary key,v number);  
  2. SQL> insert into t values(1,2);  
  3. SQL> insert into t values(3,4);  
  4. SQL> commit;  
  5. SQL> select * from t;  
  6.          I          V  
  7. ---------- ---------------------------  
  8.          1          2  
  9.          3          4  

現在表中有兩條記錄,然後給它插主鍵為空或相同的值

  1. SQL> insert into t values('',10);  
  2. ERROR at line 1:  
  3. ORA-01400: cannot insert NULL into ("Y"."T"."I")  
  4. SQL> insert into t values(1,10);  
  5. ERROR at line 1:  
  6. ORA-00001: unique constraint (Y.PK_I) violated  

可以看到全部報錯,此時主鍵不能為空或重復

查看是否建立索引

  1. SQL> select index_name from user_indexes;  
  2. INDEX_NAME  
  3. ------------------------------  
  4. PK_I  

把約束關閉再次做同樣的操用

  1. SQL> alter table t disable novalidate constraint pk_i;  
  2. Table altered.  
  3. SQL> insert into t values('',10);  
  4. 1 row created.  
  5. SQL> insert into t values(1,10);  
  6. 1 row created.  
  7. SQL> commit;  
  8. Commit complete.  
  9. SQL> select * from t;  
  10.          I          V  
  11. ---------- ----------  
  12.          1          2  
  13.          3          4  
  14.                    10  
  15.          1         10  
  16. SQL>  select index_name from user_indexes;  
  17. no rows selected  

可見當把約束關閉後就可以何意給表插數據了,而具索引也自動刪除了。

現在激活約束

  1. SQL> alter table t enable validate constraint pk_i;  
  2. alter table t enable validate constraint pk_i  
  3. ERROR at line 1:  
  4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  

因為表中主鍵有相同的值所以不能恢復到enable validate狀態了

再次測試回復到enable novalidate

  1. SQL> alter table t enable novalidate constraint pk_i;  
  2. alter table t enable validate constraint pk_i  
  3. ERROR at line 1:  
  4. ORA-02437: cannot validate (SYS.PK_I) - primary key violated  

也失敗了,

因為表中主鍵有了空值和相同的值,所以恢復不到enable validate狀態,但enable novalidate不檢查舊數據所以應該還能恢復到enable novalidate.

要想恢復到enable novalidate必須建立主鍵索引(關閉約束時自動刪除的那個索引)如下:

  1. SQL> create index pk_i on t(i);  
  2. Index created.  

然後恢復到enable disvalidate,以後再插數據不能為空,主鍵也不能重復了.

  1. SQL> alter table t enable novalidate constraint pk_i;  
  2. Table altered.  
  3. SQL> insert into t values(1,14);  
  4. insert into t values(1,14)  
  5. ERROR at line 1:  
  6. ORA-00001: unique constraint (SYS.PK_I) violated  

2.修正約束數據

當給一個表作主鍵約束時,因為已存數據不滿足約束規則,會提示錯誤信息,些時必須對數據進行修正

要修正數據先找出不滿足約束的數據

如下表,有不滿足約束的數據

  1. SQL> select * from t;  
  2.          I          V  
  3. ---------- ------------------------  
  4.          1          2  
  5.          3          4  
  6.          15         12  
  7.          15         10  

如果一個表數據量多可通過如下方法查找

  1. SQL> alter table t drop constraint pk_i;    
  2. Table altered.  
  3. SQL>conn y / 123  
  4. SQL> @$Oracle_HOME/rdbms/admin/utlexcpt.sql  
  5. Table created.  
  6. SQL> alter table t add constraint pk_i primary key (i) exceptions into exceptions;  
  7. select * from t where rowid in (select row_id from exceptions)  
  8.          I          V  
  9. ---------- ------------------------  
  10.          15         12  
  11.          15         10  

找到了重復的記錄

修正

  1. SQL>update t set i=10 where v=12;  
  2. SQL> select * from t;  
  3.          I          V  
  4. ---------- ----------  
  5.          1          2  
  6.          3          4  
  7.          10         12  
  8.          15         10  

再建主鍵約束

  1. alter table t add constraint pk_i primary key (i)  
  2. Table altered.  

成功了!!!

二:分區表管理

作用:將在張大表的數據分布到多個表分區段,不同分區彼此獨立,從而提高了表的可用性和性能

種類:范圍分區,散列分區(使用HASH算法,最常使用),列表分區,范圍/散列組合分區,范圍/列表組合分區

范圍分區表

創建范圍分區表

  1. create table t(v number,b number)  
  2. partition by range(v) (  
  3. partition p1 values less than ('11') tablespace test1,  
  4. partition p2 values less than ('21') tablespace test2);  

增加與刪除分區

#增加分區

  1. alter table t add partition p3 values less than ('31') tablespace test3;  
  2. alter table t drop partition p3  

一個時間分區的例子

  1. alter session set nls_data_lanage=AMERICAN;  
  2. alter session set nls_data_format='DD-MON-YYYY' 
  3. create table t(v_date date,b number)  
  4. partition by range(v_date)(  
  5. partition p1 values less than ('01-APR-2009') tablespace test1,  
  6. partition p2 values less than ('01-JUN-2009') tablespace test2);  

2.散列分區表(最常用)

創建

  1. create table t1(  
  2. v number,b number)  
  3. partition by hash(v)  
  4. (partition p1 tablespace test1,  
  5. partition p2 tablespace test2);  

增加分區

  1. alter table t add partition p3 tablespace test3;  

刪除分區

  1. alter table t drop coalesce partition;  

3.列表分區

建列表分區

  1. create table t(  
  2. v varchar2(10),  
  3. b number  
  4. )partition by list(v)  
  5. (partition p1 values('a','b') tablespace test1,  
  6. partition p2 values('c','d') tablespace test2);  

#插入數據

  1. SQL> insert into t values('a',10);  
  2. SQL> insert into t values('d',20); 

#注意,插入數據時第一個字段只能為a,b,c,d

  1. SQL> insert into t values('f',30);  
  2. ERROR at line 1:  
  3. ORA-14400: inserted partition key does not map to any partition  

#查詢

  1. select * from t;  
  2. select * from t partition(p1);  
  3. select * from t partition(p2);  
  4. select * from t where v=XXX  

增加分區

  1. alter table t add partition p3 values('31','32') tablespace test3;  

刪除分區

  1. alter table t drop partition p3  

4.范圍/散列組合分區

建立散列組合分區

  1. create table t(  
  2. v number,b number)  
  3. partition by range(v)  
  4. subpartition by hash(b) subpartitions 2  
  5. store in (test1,test2)(  
  6. partition p1 values less than ('11'),  
  7. partition p2 values less than ('21'));  

查詢

  1. select * from t;  
  2. select * from t partition(p1);  
  3. select * from t where ....  

增加主分區和子分區

  1. alter table t add partition p3 values less than ('31') tablespace test3;  
  2. alter table t modify partition p3 add subpartition;  

刪除分區

  1. alter table t coalesce partition;  
  2. alter table t modify partition p1 coalesce subpartition;  

5.范圍/列表組合分區

創建

  1. create table t(  
  2. v number,b number)  
  3. partition by range(v)  
  4. subpartition by list(b)  
  5. (partition p1 values less than ('11') tablespace test1(  
  6. subpartition p1_1 values('1','3'),  
  7. subpartition p1_2 values('5','6')  
  8. ),  
  9. partition p2 values less than ('21') tablespace test2(  
  10. subpartition p2_1 values('13','14'),  
  11. subpartition p2_2 values('15','16')  
  12. ));  

查詢

  1. select * from t  
  2. select * from t partition(p1)  
  3. select * from t subpartition(p1_1)  
  4. select * from t where .....  
  5. select segment_name,partition_name,tablespace_name  
  6. from user_segments where segment_name='T';  

增加分區和子分區

  1. alter table t add partition p3 values less than ('31') tablespace test3(  
  2. subpartition p3_1 values('25','26'),  
  3. subpartition p3_2 values('22','23'));  
  4. alter table t modify partition r3  
  5. add subpartition r3_3 tablespace test3 values('28','29');  

刪除分區

  1. alter table t modify partition p1 coalesce subpartition;  

其它設置

  1. 交換分區數據  
  2. alter table t Exchange partition p1 with table tt;  
  3. 載斷分區  
  4. alter table t truncate partition p1;  
  5. 修改分區名  
  6. alter table t rename partition p2_1 to p2;  
  7. 合並分區  
  8. alter table t merge partitions p1,p2 into partition p01  
  9. 重組分區  
  10. alter table t move partition p1 tablespace test04  
  11. 為列表分區和子分區加值  
  12. alter table t modify partition p1 add values('111');  
  13. alter table t modify subpartition p3_1 add values('111');  
  14. 從列表分區和子分區中刪除值  
  15. alter table t modify partition p1 drop values('111')  
  16. alter table t modify subpartition p3_1 drop values('111')  

分區表常用的數據字典

  1. 分區表信息:  dba_part_tables  
  2. 顯示分區:    dba_tab_partitions  
  3. 顯示子分區:  dba_tab_subpartitions  
  4. 顯示分區列:  dba_part_key_columns  
  5. 顯示子分區列:dba_subpart_dey_columns  
  6. 顯示分區索引:dba_part_indexes  
  7. 顯示索引分區:dba_ind_partitions  
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved