使用 CASCADE CONSTRAINTS选项删除表空间时对数据库对象影响的探查

转载 2012年03月23日 09:39:16

今天有朋友问到这样一个问题,带有“CASCADECONSTRAINTS”关键字的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.小结
本人极力推崇的精神是:“使用自己的双手寻找自己想要的真相”,不要人云亦云亦或停滞在猜想阶段。他人的经验固然可贵,但是,如何将这些宝贵经验转化为自己的财富?如何在遇到问题并且没有前人经验可借鉴时伊然淡定自如?——纸上得来终觉浅,绝知此事要躬行!

转载地址:

http://space.itpub.net/519536/viewspace-664652

cascade constraints和purge简述

1、 cascade constraints DROP TABLE ** CASCADE CONSTRAINTS PURGE 假设A为主表(既含有某一主键的表),B为从表(即引用了A的主键作为外键)。...
  • bbqk9
  • bbqk9
  • 2011年04月29日 10:53
  • 3989

ORACLE中Drop table cascade constraints之后果

ORACLE中Drop table cascade constraints
  • ytfy12
  • ytfy12
  • 2016年09月06日 10:40
  • 592

不要让临时表空间影响数据库性能

一般Oracle数据库(Oracle Database)可以分为两部分,即实例(Instance)和数据库(Database)。 实例:是一个非固定的、基于内存的基本进程与内存结构。当服务器关闭后,实...

Oracle不要让临时表空间影响Oracle数据库性能

不要让临时表空间影响Oracle数据库性能 Oracle数据库中进行排序、分组汇总、索引等到作时,会产生很多的临时数据。如有一张员工信息表,数据库中是安装记录建立的时间来保存的。如果用户查询时,使用...

不要让临时表空间影响数据库性能

在Oracle数据库中进行排序、分组汇总、索引等到作时,会产生很多的临时数据。如有一张员工信息表,数据库中是安装记录建立的时间来保存的。如果用户查询时,使用Order BY排序语句指定按员工编号来排序...

不要让临时表空间影响Oracle数据库性能

在Oracle数据库中进行排序、分组汇总、索引等到作时,会产生很多的临时数据。如有一张员工信息表,数据库中是安装记录建立的时间来保存的。如果用户查询时,使用Order BY排序语句指定按员工编号来排序...

数据库开发——参照完整性——在外键中使用Delete on cascade选项

原文: http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/?utm_sou...

oracle使用cmd命令导入数据库、删除用户、删除表空间

首先确认数据库文件是全库导出(比如原数据库的实例名为ORCL,则以下创建的实例就是要ORCL,要是原实例为TEXT,则创建的实例就要是TEXT) 前置条件:创建数据库实例(此处使用ORCL作例子,注意...

【IMPDP】使用 TRANSFORM选项去掉表空间和存储子句

使用IMPDP工具完成数据导入时,会按照dump文件中有关的存储的参数信息完成数据的导入。很多情况下我们希望按照被导入用户的默认参数完成数据的导入,此时我们可以使用IMPDP的TRANSFORM参数辅...
  • xu1314
  • xu1314
  • 2012年10月10日 14:37
  • 6269
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用 CASCADE CONSTRAINTS选项删除表空间时对数据库对象影响的探查
举报原因:
原因补充:

(最多只允许输入30个字)