Question

如何删除表空间ora-02449

Answer 1

您好，很高兴为您解答。

SQL> drop tablespace csmbus including contents cascade constraints;
drop tablespace csmbus including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

问题模拟：
SQL> create tablespace test1 datafile 'F:oracleproduct10.1.0oradatachangtest1.dbf' size 10m;
Tablespace created.
SQL> create index t2_ind on t2(object_id);
Index created.
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
表的索引与表所处表空间对表空间删除没有影响。
SQL>
SQL> create table t
2 tablespace test
3 as select * from all_objects;
Table created.
SQL> alter table t add primary key(object_id)
2 using index
3 tablespace test1;
Table altered.
SQL> drop tablespace test1 including contents and datafiles;
drop tablespace test1 including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
表t在test表空间,表t的主键索引在test1表空间。删除test1表空间出错。

create table t
tablespace test
as select * from all_objects;
SQL> create table t2(id number primary key,name varchar2(30),object_id references t(object_id))
2 tablespace test1;
Table created.
SQL> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace test1 including contents and datafiles;
Tablespace dropped.
SQL>
表t在test表空间，表t2在test1表空间，表t2的外键引用表t的主键。删除表空间test1出错，但可以删除表空间test,即主表所在表空间可以删除。

这都是约束对表空间删除的影响，因此遇到该类错误试，应先删除约束，在删除表空间。

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】

希望我的回答对您有所帮助，望采纳！

~ O(∩_∩)O~

Answer 2

很高兴为您解答。

SQL> drop tablespace csmbus including contents cascade constraints；
drop tablespace csmbus including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

问题模拟:
SQL> create tablespace test1 datafile 'F:oracleproduct10.1.0oradatachangtest1.dbf' size 10m；
Tablespace created.
SQL> create index t2_ind on t2(object_id)；
Index created.
SQL> drop tablespace test including contents and datafiles；
Tablespace dropped.
表的索引与表所处表空间对表空间删除没有影响。
SQL>
SQL> create table t
2 tablespace test
3 as select * from all_objects；
Table created.
SQL> alter table t add primary key(object_id)
2 using index
3 tablespace test1；
Table altered.
SQL> drop tablespace test1 including contents and datafiles；
drop tablespace test1 including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
表t在test表空间，表t的主键索引在test1表空间。删除test1表空间出错。

create table t
tablespace test
as select * from all_objects；
SQL> create table t2(id number primary key,name varchar2(30)，object_id references t(object_id))
2 tablespace test1；
Table created.
SQL> drop tablespace test including contents and datafiles；
drop tablespace test including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace test1 including contents and datafiles；
Tablespace dropped.
SQL>
表t在test表空间，表t2在test1表空间，表t2的外键引用表t的主键。删除表空间test1出错，但可以删除表空间test，即主表所在表空间可以删除。

这都是约束对表空间删除的影响，因此遇到该类错误试，应先删除约束，在删除表空间。

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】
。