【TABLESPACE】使用 CASCADE CONSTRAINTS選項刪除表空間時對資料庫物件影響的探查
今天有朋友問到這樣一個問題，帶有“CASCADE CONSTRAINTS”關鍵字的DROP TABLESPACE命令是否會把與之關聯的表一同刪除？
從字面上理解，該選項應該可以將對應的約束刪除，那麼，帶有“on delete cascade”性質的子表資料會隨之刪除麼，進一步，關聯的子表會被刪除麼？
首先這是一個追求細緻並充滿好奇心問題，不過如果在提出這個問題之前如能夠自己動手做一個實驗並簡單閱讀一下官方文件中的說明，也許自己就可以給自己一個滿意、真實並且記憶深刻答案。
我們來用實驗的方式回答一下這個問題。
1.建立兩個用於實驗的表空間TBS_SEC_01和TBS_SEC_02
sys@ora10g> create tablespace TBS_SEC_01 datafile '/oracle/oradata/ora10g/tbs_sec_01.dbf' size 10m;
Tablespace created.
sys@ora10g> create tablespace TBS_SEC_02 datafile '/oracle/oradata/ora10g/tbs_sec_02.dbf' size 10m;
Tablespace created.
2.分別在兩個表空間上建立主外來鍵參照表T_PARENT和T_CHILD
1）在表空間TBS_SEC_01上建立主表T_PARENT，並初始化三條記錄
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> create table t_parent (parent_id int primary key, name varchar2(10)) tablespace TBS_SEC_01;
Table created.
sec@ora10g> insert into t_parent values (1,'record1');
1 row created.
sec@ora10g> insert into t_parent values (2,'record2');
1 row created.
sec@ora10g> insert into t_parent values (3,'record3');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_parent;
PARENT_ID NAME
---------- ------------------------------
1 record1
2 record2
3 record3
3 rows selected.
2）在表空間TBS_SEC_02上建立具有“on delete cascade”選項的子表T_CHILD，並初始化一條資料
sec@ora10g> create table t_child (child1_id int primary key, parent_id int) tablespace TBS_SEC_02;
Table created.
sec@ora10g> alter table t_child add constraint FK_t_child foreign key (parent_id) references t_parent (parent_id) on delete cascade;
Table altered.
sec@ora10g> insert into t_child values (1,1);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_child;
CHILD1_ID PARENT_ID
---------- ----------
1 1
3）確認表空間TBS_SEC_01和TBS_SEC_02上的資料庫物件資訊
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_01';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_PARENT TABLE TBS_SEC_01
SYS_C00129989 INDEX TBS_SEC_01
2 rows selected.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_02';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_CHILD TABLE TBS_SEC_02
SYS_C00129990 INDEX TBS_SEC_02
2 rows selected.
4）關注一下子表T_CHILD的DDL建立語句及約束資訊
（1）子表T_CHILD的DDL建立語句
sec@ora10g> select dbms_metadata.get_ddl('TABLE','T_CHILD','SEC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_CHILD','SEC')
-----------------------------------------------------------------
CREATE TABLE "SEC"."T_CHILD"
( "CHILD1_ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
PRIMARY KEY ("CHILD1_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02" ENABLE,
CONSTRAINT "FK_T_CHILD" FOREIGN KEY ("PARENT_ID")
REFERENCES "SEC"."T_PARENT" ("PARENT_ID") ON DELETE CASCADE ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02"
（2）子表T_CHILD上的約束資訊
sec@ora10g> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name = 'T_CHILD';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C00129995 P T_CHILD
FK_T_CHILD R T_CHILD
2 rows selected.
3.表空間刪除影響測試
1）不使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01
此時如果不使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01，將會收到ORA-02449錯誤提示，提示很清晰的描述了因存在子表引用了這個表空間中的表導致無法刪除。
sys@ora10g> drop tablespace TBS_SEC_01 including contents and datafiles;
drop tablespace TBS_SEC_01 including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
2）使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01
如果使用“CASCADE CONSTRAINTS”選項，表空間便可以刪除成功。
sys@ora10g> drop tablespace TBS_SEC_01 including contents and datafiles cascade constraints;
Tablespace dropped.
4.驗證刪除影響
1）檢視錶空間上的資料庫物件
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_01';
no rows selected
因為該表空間已經被刪除，因此無法查詢到任何記錄.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_02';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_CHILD TABLE TBS_SEC_02
SYS_C00129990 INDEX TBS_SEC_02
可見，TBS_SEC_02表空間上的資料庫物件伊然完好。
2）分別檢視錶中的資料
sec@ora10g> select * from t_parent;
select * from t_parent
*
ERROR at line 1:
ORA-00942: table or view does not exist
原本儲存在表空間TBS_SEC_01上的表T_PARENT已經隨表空間的刪除而消失。
sec@ora10g> select * from t_child;
child_id PARENT_ID
---------- ----------
1 1
1 row selected.
子表T_CHILD及其內容沒有影響。
3）確認約束刪除情況
但是此時子表上的外來鍵約束已經不復存在。
sec@ora10g> select dbms_metadata.get_ddl('TABLE','t_child','SEC') from dual;
DBMS_METADATA.GET_DDL('TABLE','t_child','SEC')
-----------------------------------------------------------------
CREATE TABLE "SEC"."t_child"
( "child_id" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
PRIMARY KEY ("child_id")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02"
sec@ora10g> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name = 'T_CHILD';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C00129995 P T_CHILD
此時子表T_CHILD僅剩主鍵約束，外來鍵參照約束已經隨“CASCADE CONSTRAINTS”選項與表空間而去~~~
5.進一步的思考
如果在上述實驗中我們使用不帶“CASCADE CONSTRAINTS”選項刪除的表空間是TBS_SEC_02而不是TBS_SEC_01能否成功呢？
這裡不贅述，相信勇於最求真實性的您一定會透過自己的雙手得到自己的答案。（悄悄的告訴你：答案是肯定的）
6.跳出實驗，看看Oracle官方文件中是如何描述這個選項的
官方文旦關於這個引數的描述資訊如下：
CASCADE CONSTRAINTS
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints from tables outside tablespace that refer to primary and unique keys of tables inside tablespace. If you omit this clause and such referential integrity constraints exist, then Oracle Database returns an error and does not drop the tablespace.
參考連結：
此時的您是不是對這段e文描述有一種豁然開朗的感覺。
7.小結
本人極力推崇的精神是：“使用自己的雙手尋找自己想要的真相”，不要人云亦云亦或停滯在猜想階段。他人的經驗固然可貴，但是，如何將這些寶貴經驗轉化為自己的財富？如何在遇到問題並且沒有前人經驗可借鑑時伊然淡定自如？——紙上得來終覺淺，絕知此事要躬行！
Good luck.
secooler
10.06.07
-- The End --
從字面上理解，該選項應該可以將對應的約束刪除，那麼，帶有“on delete cascade”性質的子表資料會隨之刪除麼，進一步，關聯的子表會被刪除麼？
首先這是一個追求細緻並充滿好奇心問題，不過如果在提出這個問題之前如能夠自己動手做一個實驗並簡單閱讀一下官方文件中的說明，也許自己就可以給自己一個滿意、真實並且記憶深刻答案。
我們來用實驗的方式回答一下這個問題。
1.建立兩個用於實驗的表空間TBS_SEC_01和TBS_SEC_02
sys@ora10g> create tablespace TBS_SEC_01 datafile '/oracle/oradata/ora10g/tbs_sec_01.dbf' size 10m;
Tablespace created.
sys@ora10g> create tablespace TBS_SEC_02 datafile '/oracle/oradata/ora10g/tbs_sec_02.dbf' size 10m;
Tablespace created.
2.分別在兩個表空間上建立主外來鍵參照表T_PARENT和T_CHILD
1）在表空間TBS_SEC_01上建立主表T_PARENT，並初始化三條記錄
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> create table t_parent (parent_id int primary key, name varchar2(10)) tablespace TBS_SEC_01;
Table created.
sec@ora10g> insert into t_parent values (1,'record1');
1 row created.
sec@ora10g> insert into t_parent values (2,'record2');
1 row created.
sec@ora10g> insert into t_parent values (3,'record3');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_parent;
PARENT_ID NAME
---------- ------------------------------
1 record1
2 record2
3 record3
3 rows selected.
2）在表空間TBS_SEC_02上建立具有“on delete cascade”選項的子表T_CHILD，並初始化一條資料
sec@ora10g> create table t_child (child1_id int primary key, parent_id int) tablespace TBS_SEC_02;
Table created.
sec@ora10g> alter table t_child add constraint FK_t_child foreign key (parent_id) references t_parent (parent_id) on delete cascade;
Table altered.
sec@ora10g> insert into t_child values (1,1);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_child;
CHILD1_ID PARENT_ID
---------- ----------
1 1
3）確認表空間TBS_SEC_01和TBS_SEC_02上的資料庫物件資訊
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_01';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_PARENT TABLE TBS_SEC_01
SYS_C00129989 INDEX TBS_SEC_01
2 rows selected.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_02';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_CHILD TABLE TBS_SEC_02
SYS_C00129990 INDEX TBS_SEC_02
2 rows selected.
4）關注一下子表T_CHILD的DDL建立語句及約束資訊
（1）子表T_CHILD的DDL建立語句
sec@ora10g> select dbms_metadata.get_ddl('TABLE','T_CHILD','SEC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T_CHILD','SEC')
-----------------------------------------------------------------
CREATE TABLE "SEC"."T_CHILD"
( "CHILD1_ID" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
PRIMARY KEY ("CHILD1_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02" ENABLE,
CONSTRAINT "FK_T_CHILD" FOREIGN KEY ("PARENT_ID")
REFERENCES "SEC"."T_PARENT" ("PARENT_ID") ON DELETE CASCADE ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02"
（2）子表T_CHILD上的約束資訊
sec@ora10g> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name = 'T_CHILD';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C00129995 P T_CHILD
FK_T_CHILD R T_CHILD
2 rows selected.
3.表空間刪除影響測試
1）不使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01
此時如果不使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01，將會收到ORA-02449錯誤提示，提示很清晰的描述了因存在子表引用了這個表空間中的表導致無法刪除。
sys@ora10g> drop tablespace TBS_SEC_01 including contents and datafiles;
drop tablespace TBS_SEC_01 including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
2）使用“CASCADE CONSTRAINTS”選項直接刪除表空間TBS_SEC_01
如果使用“CASCADE CONSTRAINTS”選項，表空間便可以刪除成功。
sys@ora10g> drop tablespace TBS_SEC_01 including contents and datafiles cascade constraints;
Tablespace dropped.
4.驗證刪除影響
1）檢視錶空間上的資料庫物件
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_01';
no rows selected
因為該表空間已經被刪除，因此無法查詢到任何記錄.
sec@ora10g> select segment_name,SEGMENT_TYPE,tablespace_name from user_segments where tablespace_name = 'TBS_SEC_02';
segment tablespace
name SEGMENT_TYPE name
------------------------------ ------------------ ---------------
T_CHILD TABLE TBS_SEC_02
SYS_C00129990 INDEX TBS_SEC_02
可見，TBS_SEC_02表空間上的資料庫物件伊然完好。
2）分別檢視錶中的資料
sec@ora10g> select * from t_parent;
select * from t_parent
*
ERROR at line 1:
ORA-00942: table or view does not exist
原本儲存在表空間TBS_SEC_01上的表T_PARENT已經隨表空間的刪除而消失。
sec@ora10g> select * from t_child;
child_id PARENT_ID
---------- ----------
1 1
1 row selected.
子表T_CHILD及其內容沒有影響。
3）確認約束刪除情況
但是此時子表上的外來鍵約束已經不復存在。
sec@ora10g> select dbms_metadata.get_ddl('TABLE','t_child','SEC') from dual;
DBMS_METADATA.GET_DDL('TABLE','t_child','SEC')
-----------------------------------------------------------------
CREATE TABLE "SEC"."t_child"
( "child_id" NUMBER(*,0),
"PARENT_ID" NUMBER(*,0),
PRIMARY KEY ("child_id")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBS_SEC_02"
sec@ora10g> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name = 'T_CHILD';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C00129995 P T_CHILD
此時子表T_CHILD僅剩主鍵約束，外來鍵參照約束已經隨“CASCADE CONSTRAINTS”選項與表空間而去~~~
5.進一步的思考
如果在上述實驗中我們使用不帶“CASCADE CONSTRAINTS”選項刪除的表空間是TBS_SEC_02而不是TBS_SEC_01能否成功呢？
這裡不贅述，相信勇於最求真實性的您一定會透過自己的雙手得到自己的答案。（悄悄的告訴你：答案是肯定的）
6.跳出實驗，看看Oracle官方文件中是如何描述這個選項的
官方文旦關於這個引數的描述資訊如下：
CASCADE CONSTRAINTS
Specify CASCADE CONSTRAINTS to drop all referential integrity constraints from tables outside tablespace that refer to primary and unique keys of tables inside tablespace. If you omit this clause and such referential integrity constraints exist, then Oracle Database returns an error and does not drop the tablespace.
參考連結：
此時的您是不是對這段e文描述有一種豁然開朗的感覺。
7.小結
本人極力推崇的精神是：“使用自己的雙手尋找自己想要的真相”，不要人云亦云亦或停滯在猜想階段。他人的經驗固然可貴，但是，如何將這些寶貴經驗轉化為自己的財富？如何在遇到問題並且沒有前人經驗可借鑑時伊然淡定自如？——紙上得來終覺淺，絕知此事要躬行！
Good luck.
secooler
10.06.07
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28853590/viewspace-2150792/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 刪除資料庫表空間資料庫
- 【TABLESPACE】Oracle資料庫預設永久表空間的查詢及刪除方法Oracle資料庫
- SYSAUX表空間滿對資料庫的影響以及解決措施UX資料庫
- 【Oracle-資料庫維護】-刪除臨時表空間Oracle資料庫
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 臨時表空間資料刪除問題
- dbca刪除資料庫時選項灰色資料庫
- 變更OS時間對資料庫的影響資料庫
- oracle多使用者使用同一個表空間_drop user cascade的影響Oracle
- 只讀表空間物件被刪除後對應的物件資訊物件
- 修改系統時間對oracle資料庫的影響Oracle資料庫
- ASM時的OFM特性對影的建資料檔名的影響及為SYSTEM表空間的資料檔案使用別名ASM
- 達夢資料庫之初始化頁大小對於表及表空間的影響分析資料庫
- ORACLE資料庫中刪除表資料後,資料庫表空間已使用不會自動減少Oracle資料庫
- 臨時表空間的建立、刪除，設定預設臨時表空間
- 資料庫物件遷移表空間資料庫物件
- oracle誤刪除表空間的資料檔案Oracle
- 如何刪除 Mac 儲存空間的其他選項？Mac
- oracle的臨時表空間temporary tablespaceOracle
- 刪除臨時表空間hang處理
- oracle級聯刪除使用者，刪除表空間Oracle
- 2 Day DBA-管理Oracle例項-修改表空間-刪除表空間Oracle
- 如何正確的刪除表空間資料檔案
- 刪除表空間和表空間包含的檔案
- Oracle - 資料庫的例項、表空間、使用者、表之間關係Oracle資料庫
- oracle中的資料庫、使用者、方案、表空間、表物件之間的關係Oracle資料庫物件
- 誤刪oracle資料庫表空間檔案Oracle資料庫
- COM套間對.NET程式使用COM物件的影響物件
- oracle刪除(釋放)資料檔案/表空間流程Oracle
- Mac蘋果儲存空間的其他選項該如何刪除？Mac蘋果
- MySQL 5.7的表刪除資料後的磁碟空間釋放MySql
- SQL刪除資料庫裡所有表的外來鍵，同時刪除所有使用者表SQL資料庫
- ORACLE表空間的建立修改刪除Oracle
- oracle 失誤刪掉資料檔案後，刪除表空間操作Oracle
- 臨時表空間temporary tablespace相關操作
- 檢視oracle 資料庫中的級聯刪除(delete cascade)Oracle資料庫delete
- 多例項資料庫刪除例項資料庫
- 更改當前資料庫預設臨時表空間注意事項資料庫