熱點推薦：
您现在的位置： 電腦知識網 >> 編程 >> Oracle >> 正文

管理Oracle約束與分區表

2022-06-13   來源: Oracle 

　　．約束

　　作用

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

　　分類

　　約束的種類有not nulluniqueprimary keyforeign keycheck

　　Not null確保字段值不能為空

　　Unique:確保字段值唯一性

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

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

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

　　約束狀態

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

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

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

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

　　約束常用語句


create table t(i numberv mubmer not null)
create table t(i numberv mubmer unique)
create table t(i number constraint pk_i primary keyv number)
create table t(c numberd numberconstraint fk_d foreign key(c)references t(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 ());
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;

　　#常用的數據字典


dba_constraints
dba_cons_columns
user_cons_columns
user_constraints

　　簡單應用

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


SQL> create table t(i number constraint pk_i primary keyv number);
SQL> insert into t values();
SQL> insert into t values();
SQL> commit;
SQL> select * from t;
         I          V

                  
                    

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


SQL> insert into t values();
ERROR at line :
ORA: cannot insert NULL into (YTI)
SQL> insert into t values();
ERROR at line :
ORA: unique constraint (YPK_I) violated 

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

　　查看是否建立索引


SQL> select index_name from user_indexes;
INDEX_NAME

PK_I 

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


SQL> alter table t disable novalidate constraint pk_i;
Table altered
SQL> insert into t values();
row created
SQL> insert into t values();
row created
SQL> commit;
Commit complete
SQL> select * from t;
         I          V

                  
                  
                  
                 
SQL>  select index_name from user_indexes;
no rows selected

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

　　現在激活約束


SQL> alter table t enable validate constraint pk_i;
alter table t enable validate constraint pk_i
ERROR at line :
ORA: cannot validate (SYSPK_I) primary key violated  

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

　　再次測試回復到enable novalidate


SQL> alter table t enable novalidate constraint pk_i;
alter table t enable validate constraint pk_i
ERROR at line :
ORA: cannot validate (SYSPK_I) primary key violated  

　　也失敗了

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

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


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

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


SQL> alter table t enable novalidate constraint pk_i;
Table altered
SQL> insert into t values();
insert into t values()
ERROR at line :
ORA: unique constraint (SYSPK_I) violated 

　　修正約束數據

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

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

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


SQL> select * from t;
         I          V

                  
                  
                 
                   

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


SQL> alter table t drop constraint pk_i;
Table altered
SQL>conn y /
SQL> @$ORACLE_HOME/rdbms/admin/utlexcptsql
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

                 
                 

　　找到了重復的記錄

　　修正


SQL>update t set i= where v=;
SQL> select * from t;
         I          V

                  
                  
                 
                 

　　再建主鍵約束


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

　　成功了!!!

　　二:分區表管理

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

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

　　范圍分區表

　　創建范圍分區表


create table t(v numberb number)
partition by range(v) (
partition p values less than () tablespace test
partition p values less than () tablespace test); 

　　增加與刪除分區

　　#增加分區


alter table t add partition p values less than () tablespace test;
alter table t drop partition p  

　　一個時間分區的例子


alter session set nls_data_lanage=AMERICAN;
alter session set nls_data_format=DDMONYYYY
create table t(v_date dateb number)
partition by range(v_date)(
partition p values less than (APR) tablespace test
partition p values less than (JUN) tablespace test);

　　散列分區表(最常用)

　　創建


create table t(
v numberb number)
partition by hash(v)
(partition p tablespace test
partition p tablespace test); 

　　增加分區

　　 alter table t add partition p tablespace test;  

　　刪除分區

　　 alter table t drop coalesce partition;  

　　列表分區

　　建列表分區


create table t(
v varchar()
b number
)partition by list(v)
(partition p values(ab) tablespace test
partition p values(cd) tablespace test); 

　　#插入數據


SQL> insert into t values(a);
SQL> insert into t values(d);

　　#注意插入數據時第一個字段只能為abcd


SQL> insert into t values(f);
ERROR at line :
ORA: inserted partition key does not map to any partition  

　　#查詢


select * from t;
select * from t partition(p);
select * from t partition(p);
select * from t where v=XXX  

　　增加分區

　　 alter table t add partition p values() tablespace test;

　　刪除分區

　　 alter table t drop partition p

　　范圍/散列組合分區

　　建立散列組合分區


create table t(
v numberb number)
partition by range(v)
subpartition by hash(b) subpartitions
store in (testtest)(
partition p values less than ()
partition p values less than ()); 

　　查詢


select * from t;
select * from t partition(p);
select * from t where   

　　增加主分區和子分區


alter table t add partition p values less than () tablespace test;
alter table t modify partition p add subpartition; 

　　刪除分區


　　alter table t coalesce partition;
alter table t modify partition p coalesce subpartition;

 

　　范圍/列表組合分區

　　創建


create table t(
v numberb number)
partition by range(v)
subpartition by list(b)
(partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values()
)
partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values()
));

　　查詢


select * from t
select * from t partition(p)
select * from t subpartition(p_)
select * from t where
select segment_namepartition_nametablespace_name
from user_segments where segment_name=T; 

　　增加分區和子分區


alter table t add partition p values less than () tablespace test(
subpartition p_ values()
subpartition p_ values());
alter table t modify partition r
add subpartition r_ tablespace test values();

　　刪除分區

　　 alter table t modify partition p coalesce subpartition;

　　其它設置


交換分區數據
alter table t exchange partition p with table tt;
載斷分區
alter table t truncate partition p;
修改分區名
alter table t rename partition p_ to p;
合並分區
alter table t merge partitions pp into partition p
重組分區
alter table t move partition p tablespace test
為列表分區和子分區加值
alter table t modify partition p add values();
alter table t modify subpartition p_ add values();
從列表分區和子分區中刪除值
alter table t modify partition p drop values()
alter table t modify subpartition p_ drop values() 

　　分區表常用的數據字典


分區表信息:  dba_part_tables
顯示分區:    dba_tab_partitions
顯示子分區:  dba_tab_subpartitions
顯示分區列:  dba_part_key_columns
顯示子分區列:dba_subpart_dey_columns
顯示分區索引:dba_part_indexes
顯示索引分區:dba_ind_partitions


From:http://tw.wingwit.com/Article/program/Oracle/201311/16778.html
    《管理Oracle約束與分區表》本文是由Oracle免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.