【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.

参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9004.htm#SQLRF01807

此时的您是不是 对这段e文描述 有一种豁然开朗的感觉。

7.小结
本人极力推崇的精神是：“使用自己的双手寻找自己想要的真相”，不要人云亦云亦或停滞在猜想阶段。他人的经验固然可贵，但是，如何将这些宝贵经验转化为自己的财富？如何在遇到问题并且没有前人经验可借鉴时伊然淡定自如？——纸上得来终觉浅，绝知此事要躬行！

Good luck.

secooler
10.06.07

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-664652/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-664652/