教你如何成為Oracle 10g OCP - 第九章 物件管理

第9章  物件管理

 

物件 -- 邏輯意義上的概念，如表，索引，procedure,function等都是物件。

Schema -- 這些物件放在某個使用者下，這些物件也叫schema物件，如果
使用者下沒有物件，該使用者就不存在schema . 當使用者下具有物件了，該
使用者才具有schema .

普通表 -- 一般表都會對應一個segment , 表是作為邏輯意義上存放資料
的地方，物理上來說資料存放在segment中。 表的作用在於方便我們更新
對應的segment中的資料。 

 

 

表的分類：

1. 普通表 -- 最常見的表，一個表對應一個segment. 其實普通表還有一個
名稱是Heap Organized Table堆組織表，是用來對應索引組織表的，堆組織
表中的資料行無序儲存。

2. 分割槽表 -- 這種表為虛擬表，表沒有對應的segment, 每個分割槽對應一個
segment, 建議一般將來可能大於2G左右的表都要考慮使用分割槽表。

3. 索引組織表(IOT) -- 也是一種虛擬表，簡稱IOT, 必須要有一個主鍵索引，
同時，所有的資料與主鍵列一起存放在主鍵索引的葉子節點裡，IOT表不對應
Segment, 而是透過索引來對應segment .

4. 簇表(Cluster Table) -- 虛擬表，如果有兩個主明細關係的表，比如定
單主表和明細表，這兩個表經常進行關聯查詢，這時我們可以將他們建立成
簇表，首先我們要建立一個簇，對應一個segment, 然後建立主明細表，並
將主明細表根據關聯欄位與簇建立聯絡，向兩個表插入資料時，它們會根據
關聯欄位共同存放在一個block中，也就是一個block存放了兩個表的資料，
這樣關聯查詢兩表時只需要掃描一個block。簇表本身不對應segment,而是
透過簇來對應segment 的。

 

 

9.1.1 建立普通表

char(5)型別，如果插入abc字元，儲存在庫中是'abc  ',長度照樣是5位。

建立表的時候，如果表空間啟用了ASSM(Auto Segment Space Manange), 那麼pctused失效。

initrans 預設該表的資料塊頭部ITL槽的個數(預先分配的)，而maxtrans表示最多有多少ITL.
10g或以後版本，maxtrans被廢棄，預設都為255，如果塊的空閒空間不足以讓ITL擴充套件到255個，
那麼最大隻能是受限於空閒空間，如果空閒空間足夠擴充套件到255個，那麼最大隻能是255個ITL,
也就是取可擴充套件空閒空間和255中較小的那個作為限制。
(備註： ITL - Interested Transaction List) 

對於buffer pool來說，我們可以設定該表進入哪個buffer cache,比如keep buffer cache
或者default buffer cache及recycle buffer cache 等。

 

典型建表的例子 ：

CREATE TABLESPACE LOG_DATA DATAFILE
  '/ocfs_data/mxdell/log_data01.dbf' SIZE 4001M AUTOEXTEND OFF,
  '/ocfs_ctrl_redo/mxdell/log_data02.dbf' SIZE 2048064K AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM. SIZE 20M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO       -- ASSM
FLASHBACK ON;

 

CREATE TABLE DFMS.EDIBTO850_MAIN
(
  CONTROLNUMBER     VARCHAR2(40 BYTE)  NOT NULL,
  ISA06             VARCHAR2(50 BYTE),
  CREATE_TIME       DATE        DEFAULT sysdate
)
TABLESPACE LOG_DATA
PCTUSED    0            --- 使用ASSM, pctused引數失效
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             20M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE   
NOPARALLEL
MONITORING;

 

解釋：

-- create tablespace 中的flashback on 及 flashback off .

對於create tablesapce中的FLASHBACK ON，預設情況下建立tablespace就是
啟動flashback, 指定將tablespace放在FLASHBACK模式下，Oracle會為這個
tablespace儲存閃回日誌資料，那麼在做FLASHBACK DATABASE 的時候，能夠
應用到此表空間上，如果我們不設定flashback模式，預設是FLASHBACK ON. 

指定FLASHBACK OFF將使對應的tablespace不在FLASHBACK模式下，Oracle將
不會為它儲存任何的閃回日誌資料，在做FLASHBACK DATABASE之前，我們必
須先將這個表空間中的資料檔案offline或者drop他們，或者還有一種方式,
你可以將整個tablespace offline, 在以上任何一種情況下，資料庫不刪除存
在的閃回日誌 。

備註一下： flashback database預設情況下是off的。

 

-- create table 

log_data表空間啟用了ASSM, 那麼建表的時候，沒有明確指定pctused, 預設
pctused是0，pctfree預設還是10，INITRANS 1, MAXTRANS 255 表示該表的
(每個)資料塊頭部最小和最大的ITL槽(Interested Transaction List), 一般
為了最佳化ITL,可以設定INITRANS為3或4即可(足夠)。關於ITL可以參考下面的鏈
接。PCTINCREASE在autoallocate下起作用，表示下一個extent大小為前一個
的(1+pctincrease%)倍 ，在uniform. size設定的tablespace下不起作用，Oracle
建議使用PCTINCREASE 的零設定 . 參考：
http://space.itpub.net/35489/viewspace-671133 

 

BUFFER POOL ----- 
包括DEFAULT、KEEP、RECYCLE三個POOL，其大小分別對應三個引數：DB_CACHE_SIZE,
DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SZIE，這三個POOL相互獨立，沒有包含的關係。
語法如下：
create/alter table ...... storage(buffer_pool default);
create/alter table ...... storage(buffer_pool keep);
create/alter table ...... storage(buffer_pool recycle); 

default -- 普通的buffer cache .

recycle -- 通常將偶爾訪問的大表放入recycle pool中，這樣做是為了防止它的一次訪問
就將那些頻繁使用且應該長時間留在buffer cache中的物件清除出記憶體。

keep -- 當表被讀入的時候，放入keep pool，keep pool 中的資料會盡量長時間的保留，
保留的時間長短依據keep pool的負載而定。對於放入KEEP POOL中的表資料，要麼全表放
入，要麼全表都out，不會只放入一部分資料。通常將經常訪問的物件放入keep pool中。

 

KEEP POOL 及 CACHE 引數的聯絡及區別 ---

建表時我們在最後可以看到有NOCACHE(預設)，如果是cache呢，與storage中的
keep pool 有什麼區別呢？ 

create/alter table ...... cache;
設定表為cache時，當全表掃描時，將物件資料放入default pool的LRU熱端(備註：
如果是nocache，則放入LRU冷端，很快就會被age out)，cache適合頻繁使用的小
表， 如果有太多的表cache, LRU連結串列會很擁擠，LRU機制也會受影響，所以該把表
放入keep pool，而且cache子句也不適合大表，因為有可能這個大表會佔據整個
LRU連結串列。 

當BUFFER_POOL和CACHE同時使用時，KEEP比NOCACHE有優先權。BUFFER_POOL用來指
定存貯的緩衝池，而CACHE/NOCACHE指定儲存的方式。建表時候不註明的話，NOCACHE
是預設值。

CACHE
For data that is accessed frequently, this clause indicates that the blocks
retrieved for this table are placed at the most recently used end of the
least recently used (LRU) list in the buffer cache when a full table scan
is performed. This attribute is useful for small lookup tables.

NOCACHE 
For data that is not accessed frequently, this clause indicates that the
blocks retrieved for this table are placed at the least recently used end
of the LRU list in the buffer cache when a full table scan is performed.
NOCACHE is the default for LOB storage.

附註：對於8i，需要設定引數buffer_pool_keep，還要修改 db_block_lru_latches，
該引數預設是1，無法使用buffer_pool_keep。該引數應該比2*3*CPU數量少，但是要
大於 1，才能使用buffer_pool_keep。buffer_pool_keep從db_block_buffers中分配，
因此也要小於db_block_buffers。

 

 

NOCOMPRESS  ---
這個設定只對heap-organized tables即普通表有作用，用於壓縮data segments，這個
引數對於哪些update及insert操作比較少的data warehouses 特別有用，COMPRESS 設定
啟動 table compression.  關鍵字NOCOMPRESS 關閉 table compression. NOCOMPRESS
是預設值。 當設定為COMPRESS時，Oracle嘗試在 direct-path INSERT 操作的時候壓縮
資料，imp操作不支援 direct-path INSERT 操作，因此不能在壓縮格式下imp資料。

NOPARALLEL --- 
指定NOPARALLEL用於序列執行，預設為NOPARALLEL。如果你想oracle選擇一個並行度，
它等於所有例項上的可用的CPU數乘以初始化引數 PARALLEL_THREADS_PER_CPU 的值。

PARALLEL integer ---

指定parallel度的值，它是用於parallel操作中的並行執行緒，每個並行執行緒可能使用一個或
兩個parallel execution servers，一般來說，Oracle會計算最佳並行度，所以我們沒有必要
指定這個值 。

 

MONITORING ---  

在早期版本中，可以使用這些子句去開啟或關閉在table上的修改統計(監控)，這個子句已經被摒棄。
如果以前你透過DBMS_STATS 包來收集表上的修改統計(in GATHER AUTO or GATHER STALE mode)，
那麼現在你不必做任何事情，現在Oracle資料庫自動收集這些統計資訊，在現有的程式碼中MONITORING
和 NOMONITORING 不會引起錯誤。 

如果因為效能原因，你不想在任何tables上收集修改統計資訊，那麼你應該設定初始話引數STATISTICS_LEVEL
為BASIC, 但是，小心，這樣做會失去很多管理上的方便(比如自動統計資訊) 。

 

 

資料行 ---

表建立好了之後，我們就可以向表裡插入資料了，每個資料行在資料塊中的結構如下：

[行頭部]+[列長度]+[列資料]+[列長度]+[列資料]+[列長度]+[列資料]+..... 

行頭部 -- 存放了該行包含的列的數量，以及鎖定標記等，當某個事務更新某行記錄
時，會在行頭部記錄所使用的ITL槽號 (表的資料塊頭部ITL槽)以及鎖定標記。

行目錄(row directory) -- 在資料塊頭部存在一個結構，即行目錄，為這個塊中的
每個資料行都記錄了一個條目，每個條目就指向該記錄的行頭部，還包括記錄了這個
塊中的row數量及table數量，每個行的在塊中的偏移量(距離頭部)等等， 所以Oracle
能夠區分不同的行。

 

ROWID列 --- 

ROWID是一個偽列，該列的值並沒有真正的儲存在資料塊中，但是可以查詢並顯示出來。

ROWID的格式 OOOOOOFFFBBBBBBRRR , OOOOOO表示行所在的物件號，FFF表示行所在檔案號，
BBBBBB表示該行所在的資料塊號，RRR表示行在資料塊中的行號，rowid採用64進位制來表示，

SQL> select  rowid , book_id  from  books ;

ROWID                 BOOK_ID
-----------------------------------------
AAAM0hAAEAAAAGnAAA          1

可以看出插入的記錄的rowid為 [AAAM0h][AAE][AAAAGn][AAA]

我們可以透過dbms_rowid包來驗證：

select dbms_rowid.rowid_relative_fno('AAAM0hAAEAAAAGnAAA') as 'File No' from dual;
select dbms_rowid.rowid_block_number('AAAM0hAAEAAAAGnAAA') as 'File No' from dual;
select dbms_rowid.rowid_row_number('AAAM0hAAEAAAAGnAAA') as 'File No' from dual;
select dbms_rowid.rowid_relative_fno('AAAM0hAAEAAAAGnAAA') as 'File No' from dual;

 

9.1.2  管理普通表

擴充套件表 - 

有時候我們需要主動擴充套件一個表所佔用的空間，或者要將一個表分佈到多個資料檔案上從而
將I/O分散到多個磁碟上，可以執行以下命令：

alter table books allocate extent (size 10M  datafile '/data/orcl/users02.dbf'); 
alter table books allocate extent;  表示在當前資料檔案中擴充套件extent ;

重整表 - 

有時候我們需要消除表的資料塊級別的碎片(即每個資料塊含的資料行太少，比如100行分佈在
100個資料塊中，我們稱為稀疏表)，或者一個表從目前的表空間轉移到另外一個表空間。

稀疏表產生的原因：  
表中存在很多insert及delete操作，在表的segment header中記錄了一個值HWM(High Water Mark),
HWM表示當前segment中使用的最後一個資料塊的位置，單segment中插入資料，不斷擴充套件資料塊，
需要不斷增加HWM值. 使用HWM的好處在於，使用者select count(*) 或其他語句引起全表掃描時，
伺服器在掃描segment過程中，只掃描到HWM為止，HWM後面的資料塊還沒有被使用過，不含有資料，
不用掃描。 我們在delete資料的時候，不會降低HWM的值，即使全部刪除，也不會降低HWM,
全表掃描的時候還是會掃描到HWM的位置。

長時間的insert及delete可能導致資料不緊密，因此我們需要重新整理資料行在資料塊中的分佈，
使他排列更加緊密一些。 Oracle10g之前，我們僅能使用CTAS(create table as select)，move
或匯入匯出方式來降低HWM, Oracle10g或以後可以使用shrink來對錶進行收縮。

附： 簡單介紹一下CTAS,move 

CTAS : 

create table vbap_sorted tablespace vbap_copy
storage (initial 500m  next 50m maxextents unlimited)
parallel (degree 4)
as 
select *
from  sapr3.vbap order by  mandt, vbeln, posnr;

 

 

MOVE :

alter table table_name move ...
aleter table move 只是給表中所有資料搬到新的儲存空間上,就相當於把A房間內亂七八糟的東西
整理起來放到B房間裡面然後再把A房間讓出來給別人,這樣你不但讓出了整個A房間而且仍然只佔用
一個房間,但是你的東西(資料)也整齊了可能只佔用B房間很少的一部分,也可以在同一表空間move.
將表move後，改變了原表中儲存的物理屬性，該表上的索引全都失效，需重建，且需要重新分析表。
eg:   alter table table_name move;  在當前表空間進行重整。 

ALTER TABLE...MOVE  是針對segment而言的，可以對非分割槽表重新設定資料的存放位置、可以對
分割槽表的一個分割槽移動到新的段中，甚至你能夠轉移到不同的表空間中。

移動表會導致行的rowid變化。這導致該表上面的index不可用，即標記為UNUSABLE，當用DML來
操作該表時用到該索引，會引發ORA-01502 錯誤, 索引必須rebuild。該表的統計資訊也會失效，
所以需要重新對該表進行統計分析。因為rowid的變化，所以如果此表上有物化檢視，也需要重
建。move 過程並沒有引起資料的變化，不會引起觸發器的觸發。 

SKIP_UNUSABLE_INDEXES引數的主要用途是當索引為unusable狀態時保證sql的執行，雖然它
保證了系統的健壯性，但是有可能系統沒有使用該索引而導致效能的低下。還有該引數對於
使用hint的某些sql和唯一索引的插入、刪除語句卻不生效 。

注意： MOVE操作會根據原表的INITIAL大小為新表建立第1個EXTENT。MOVE不會自動減少表
的初始擴充套件的大小，如果原來的initial大小非常大，shrink不會收縮這部分大小即使也存
在大量稀疏塊。

oracle9i以後對分割槽表做move或者truncate的時可以用update global indexes語句來
同步更新全域性分割槽索引，用消耗一定資源來換取高度的可用性(DDL同時也更新global index
保證索引不失效)。一般比較少這樣做。

alter table xxxx move update global indexes;

 

MOVE的原理：

可以透過一下語句檢視trace檔案,從而檢視move過程oracle做了什麼 ：

SQL> alter session set events '10046 trace name context forever, level 12' ;
SQL> alter table aaaa move tablespace log_data ;
SQL> alter session set events '10046 trace name context off' ;

Move的具體原理還在研究中......

 

 

SHRINK ---

Oracle10g 之前透過move或imp/exp進行脆片處理，不管使用哪種方式，都會中斷應用，因為
在move的過程中會鎖定表，並且move結束後所有的索引失效。因此從Oracle10g以後引入了收縮
操作來消除資料塊的碎片。 儘管收縮操作是事務，但是它並沒有引起資料的變化，因此不會
引起觸發器的觸發。

進行收縮操作有兩個前提條件：

1. 表所在的表空間必須使用 ASSM 。
2. 收縮表引起了資料行在不同資料塊之間的轉移，所以被收縮的表上必須啟用row movement,
   如下： 
   alter table enable row movement;
   alter table shrink space;

收縮操作分為兩個階段，壓縮和降低HWM :
 
對於壓縮階段來說，就是將資料行緊密地插入到資料塊中。在這個階段，其他使用者可以對被收縮的
表進行DML操作；而在降低HWM階段，則實際將HWM降低到最後使用的那個資料塊上，該階段會對錶
加排他鎖，因此其他使用者不能對被收縮(降低HWM)的表進行任何操作。 兩個階段可以分開執行，
也可以同時執行。

Oracle建議在業務高峰期進行壓縮，等到業務不繁忙的時候，再降低HWM. 

對錶進行壓縮處理：
SQL> alter table t1 shrink space compact ; 

如果加入cascade選項，表示不僅壓縮表，同時還收縮表相關的其他segment .
SQL> alter table t1 shrink space cascade; 

最後在業務不繁忙時進行降低HWM操作；
SQL> alter table t1 shrink space ; 

----------------------------------------------
備註： 看了下eagle的幾篇文章，推薦move和exp，可以完全消除碎片。
而shrink則沒有那麼徹底，還是會有部分碎片無法消除。
----------------------------------------------

 

DBMS_REDEFINITION (線上重定義表) --- 

雖然是可以線上重定義table, 但是還是要選擇系統負載空閒的時候操作.
詳細參考： http://space.itpub.net/35489/viewspace-681493

 

Truncate --- 

如果需要刪除表中的所有資料，可以使用delete, delete是DML事務，需要對錶的
資料加鎖，產生重做記錄，並消耗undo表空間。因此delete命令需要消耗較多的
資源，執行較長的時間。如果準備刪除表中所有的記錄，Oracle提供了更加有效的
命令： truncate .

Truncate是一個DDL操作，該命令只更新資料字典，在資料字典中將該表所佔用的
空間記錄全部刪除；然後將表所佔用的資料塊全部釋放(可以被其他objects使用了)；
最後將表的HWM下降到最低(一般是init extent)，因為是DDL命令，不能回滾。
SQL> truncate table t ;  (預設等同於加入了drop storage) 

有時候，我們可能需要truncate一個非常巨大的表(可能是幾十G),更新資料字典非常
快，但是釋放資料塊的工作可能花費很長的時間(有可能超過幾十個小時)。在這個過
程中，被truncate 的表是不能被使用的，因此有可能導致表不可用的時間過長。

因此，Oracle提供了一個選項，在truncate時，更新完資料字典以後，不立即釋放全
部的資料塊，儘管資料塊沒有被釋放，仍然被表所擁有，但是表上的HWM已經下降到最低了。然後在系統比較空閒的時候，分多次釋放資料塊，每次釋放部分的空間，透過這種方式，將truncate的操作對系統的影響降低到最低。

在原HWM線以下佔用的block仍為此table佔用, 只更新資料字典，降低HWM .
SQL> truncate table t1 reuse storage ; 

然後，在系統空閒時釋放資料塊.
SQL> alter table t1 deallocate unused keep 50M ;
SQL> alter table t1 deallocate unused keep 30M ;
SQL> alter table t1 deallocate unused keep 20M ;
SQL> alter table t1 deallocate unused keep 10M ;
SQL> alter table t1 deallocate unused keep 0M ; 
我們在進行最後一步執行keep 0M 的時候，因為使用者已經向表T1中插入了資料，則不會把
表T1中的所有資料塊釋放，只是釋放沒有用的資料塊而已。

 

 

------------------
Truncate的原理：
------------------

truncate操作會做什麼：移動HWM，釋放extent，重新生成data_object_id。truncate之所以比delete快，是因為它根本不需要刪除資料。但是在實際中我們經常碰到truncate很慢的情況，實際上它比我們想象的要複雜。 在truncate之前，CKPT必須搜尋整個buffer cache，把這個object的dirty buffer全部寫回磁碟(這個操作在10g有了改進)。truncate會產生redo，undo，是修改資料字典產生的，不是刪除資料產生的。

Truncate操作只是改了一下資料字典上的一部份資訊, 另外將重新分配一個Extent, 並獲得一個新的Data Object ID, 並修改Segment Header中的資訊, 在資料檔案中的資料部份沒有作什麼修改, 象以前的DOS下刪除檔案一樣, 有undel工具可以將檔案找回來, 主要是因為磁碟中存放檔案的部份沒有修改. 因此對於Truncate操作如果我們知道以前的Data Object ID, 然後在資料檔案中是可以重新找回資料的.      Truncate和Windows上刪除檔案類似，更改的只是Segment Header和Inode表、 如果空間沒有重用，資料就仍然在那裡存在 ；很多系統在作刪除操作的時候
都只是做一個標誌位，不會去作給磁碟填0那樣昂貴的操作 。

實驗可以看出truncate是先降低HWM，然後釋放extent .  
http://space.itpub.net/?uid-35489-action-viewspace-itemid-681564 

備註： 

object_id和data_object_id同樣是表示資料庫物件的一個唯一標誌，但是object_id表示的是邏輯id，data_object_id表示的是物理id。只有表,索引,undo這些有實際物理儲存位置的物件才有data_object_id,而一些沒有物理屬性的object不存在data_object_id，例如procedure,
function,package,data type,db link,mv定義,view定義,臨時表,分割槽表定義等等這些object都是沒有對應著某個segment，因此它們的data_object_id都為空 。 大多數情況下兩者是相等的。
但對object進行truncate,move, rebuild等操作後，data_object_id就會發生改變，而object_id
不會改變。

 

 

問題： 
truncate 命令只是更新資料字典，釋放資料塊的動作是smon完成的 ？？？
還是說釋放資料塊也是truncate命令的一部分 ？？ 

 

 

Drop table ---  

刪除表屬於DDL(Data Definition Language)命令，也就是隻是更新資料字典的資訊，orale並不會讀取表所包含的資料塊的資訊。因此即使表出於read only的表空間，表
也是可以被刪除的。
SQL> drop table t1  ;

如果被刪除的表上具有一致性約束，比如，被刪除的表是其他表的父表，或者有其他表上建立的外來鍵，而外來鍵指向了被刪除的表，必須新增 cascade constraints 選項： 
SQL> drop table t1 cascade constraints ;
這樣不但能刪除表，同時把位於其他表上的，應用了表T1的外來鍵也給刪除了。

 

修改及刪除列 ---

SQL> alter table t1 rename column code to id ;
SQL> alter table t1 drop column code ; 
如果有其他表上的外來鍵引用了被刪除的列，則需要加入cascade constraints
SQL> alter table t1 drop column code cascade constraints ;  
在刪除列的過程中，會消耗undo, 如果資料行過多，會消耗過多資源，那麼我們可以
使用checkpoint N 來每隔N行就提交一次。比如：
SQL> alter table t1 drop column code cascade constraints checkpoint 2000;   

在刪除column的過程中，oracle會鎖定表，其他使用者不能進行DML操作，業務高峰期，如果刪除花太長時間，影響會比較嚴重，因此我們可以先將column標記為不可用，這個過程只更新資料字典的資訊，速度非常快，在業務閒的時候，我們再從物理上真正刪除列所佔用的空間。命令如下：
SQL> alter table t1 set unused column createdate ;
檢視dba_unused_col_tabs 裡記錄了表上有多少個被標記為unused的列。我們甚至
可以再次建立同名的column . 物理意義上的刪除如下：
SQL> alter table t1 drop unused columns; 
SQL> alter table t1 drop unused columns checkpoint 2000 ;   

9.1.3  約束(constraint)

非常不錯的constraint 的文章：

型別;
a. 非空，not null
b. 唯一, unique，實際是藉助索引實現的，建立唯一約束時，如果沒有Index，
   會自動建立
c. 主鍵, primary key, 值不能重複，與唯一約束的區別在於，唯一約束所在
   列可以為空，因為   在oracle中null不等於任何值，null也不等於null,所以
   唯一約束所在的列可以有多行為null,oracle認為他們是不同的，pk=unique+not null，
   主鍵也藉助索引實現。
d. 外來鍵, foreign key , 外來鍵發生在兩個表之間，父表列必須存在唯一約束或主鍵，
   外來鍵在子表上定義。
f. 檢查, check, 類似於where條件，表裡所有的記錄必須滿足指定的條件，not null也
   應該算作check約束，相當於check約束： col_name is not null .

我們在設計系統，考慮資料一致性的時候，應該優先考慮使用約束來實現，因為使用約束
是集中式的管理，比較容易控制。應用程式相對分散，可控性差。

9.1.3.1 約束的狀態

A. enable/disable, 這表示建立約束後，對錶的資料進行插入或修改時，對插入或修改後
的資料進行校驗，判斷其是否違反了約束，enable表示是，disable表示否。

B. validate/novalidate, 表示建立約束時，是否要對錶裡現存的資料進行校驗，判斷是否
違反了約束，validate 表示是，novalidate表示否。

上面兩種排列組合產生了約束的以下四種狀態:

A. enable+validate  表示既要校驗已經存在的資料，也要校驗新進入的資料。
B. enable+novalidate  表示不校驗已經存在的資料，只校驗新進入的資料。
C. disable+validate  表示不校驗新進入的資料，只校驗存在的資料。這是矛盾狀態，
   如果表上存在這種狀態，表上不能進行DML操作。
D. disable+novalidate   相當於沒有建立約束。

用的較多的前兩種狀態。對於第二種情況，一般用於資料合併時，老資料可能違反
約束，所以對老資料就不校驗了，只對新插入的資料校驗。

 

9.1.3.2  建立和管理約束

A.  enable validate :  這時需要對已經存在的資料進行校驗，以判斷是否違反了約束，
會對整個表加鎖，這時該表不能進行dml操作，必須等待校驗結束，如果已存在資料違反
了約束，則執行失敗。 一般預設建立PK後就是這種狀態。
alter table A_TEMP_T enable validate constraint PK_A_TEMP_T ; 

B.  enable novalidate :  不校驗已存在的資料，速度非常快，不對錶加鎖。
alter table A_TEMP_T enable novalidate constraint PK_A_TEMP_T ;

 

例子：

1. 建表. 
CREATE TABLE DFMS.A_TEMP_T
(
  SO_NO    VARCHAR2(30 BYTE),
  STN      VARCHAR2(35 BYTE),
  SO_QTY   NUMBER,
  NG_GLAG  VARCHAR2(10 BYTE)
)  TABLESPACE BASE_DATA ; 

2. 建立PK constraint. 
ALTER TABLE DFMS.A_TEMP_T ADD (
  CONSTRAINT  PK_A_TEMP_T  PRIMARY KEY (SO_NO)
    USING INDEX TABLESPACE BASE_IDX) ; 

3. 我們在Toad中可以看到隱含為SO_NO欄位建立了unique index.

CREATE UNIQUE INDEX DFMS.PK_A_TEMP_T ON DFMS.A_TEMP_T
(SO_NO)
LOGGING
TABLESPACE BASE_IDX ；

ALTER TABLE DFMS.A_TEMP_T ADD (
  CONSTRAINT PK_A_TEMP_T
PRIMARY KEY
(SO_NO)
    USING INDEX
    TABLESPACE BASE_IDX);

且可以看到SO_NO欄位原來定義的沒有NOT NULL, 現在在Toad中可以看到NOT NULL的標示。

 

4.  如果我們這時單獨刪除這個PK對應的unique index,那麼會報錯：
drop  index  PK_A_TEMP_T ;
ORA-02429: 無法刪除用於強制唯一/主鍵的索引

在刪除約束的時候會自動刪除PK依附的Index .
alter table DFMS.A_TEMP_T drop constraints PK_A_TEMP_T ;

 

5. 我們檢視constraints相關檢視，可以看到約束的一些值。

select * from dba_constraints where table_name='A_TEMP_T'; 

STATUS為ENABLED,  表示對插入或修改後的資料進行校驗。
DEFERRABLE值為NOT DEFERRABLE,  表示這個約束是不可延遲的。
DEFERRED為IMMEDIATE , IMMEDIATE表示DML處理完一條記錄後立即驗證 。
VALIDATED為VALIDATED , 表示要檢驗表裡已經存在的資料是否違反了約束

具體見 9.1.3.3  約束校驗資料的時機 。

 

9.1.3.3  約束校驗資料的時機 (立即生效和延遲生效)

預設情況下，一旦發生DML操作(而不是提交時)，Oracle會立即判斷變化後的資料是否違反了約束，
如果違反了，則立即回滾該DML操作進行的修改;  不過，我們還可以設定約束在提交的時候才進行
校驗，這樣的約束叫做延遲約束(deferred constraint) .

有時候我們不希望立即校驗資料，比如某表上存在一個檢查約束：單價x數量=金額，可能某種原因
需要先插入單價及數量，然後使用者會更改數量，如果立即校驗的話，前期的單價數量不能正常寫入。
那麼我們可以利用約束的延遲校驗特性。 

SQL> alter table sales add constraint chk_sales check(price*qty=value) deferrable initially deferred ;
SQL> alter table t1 add constraint pk_t1 unique(ID) DEFERRABLE initially deferred; 

上面兩個例子中：
deferrable說明約束是否可以被延遲，新增該選項說明可以延遲。
initially deferred 說明建立完畢後，何時校驗資料，initially deferred 說明提交時校驗，當然
前提是必須設定了deferrable; 而initially immediate是預設值，說明DML操作時立即校驗資料。

關於約束校驗資料的時機只存在以下幾種組合情況 ： 

DEFERRABLE INITIALLY DEFERRED：
   允許延遲驗證約束，預設方式為延遲，等待提交後驗證，可以修改為下面的第二種狀態
DEFERRABLE INITIALLY IMMEDIATE：
   允許延遲驗證約束，預設方式為立即，處理完一條記錄就立即驗證，可以修改為上面的第一種狀態
NOT DEFERRABLE：
   不允許延遲驗證約束，不能與上面兩種狀態進行轉換
NOT DEFERRABLE INITIALLY IMMEDIATE：
   和上面的第三種 NOT DEFERRABLE 是一樣的
NOT DEFERRABLE INITIALLY DEFERRED：
   這種是矛盾的情況，會報錯ORA-02447: 無法延遲不可延遲的約束條件