ORA-29857 ORA-23515 ORA-02449-优快云博客

本文介绍了在Oracle数据库中删除包含各种依赖项（如域索引、物化视图及外键约束）的表空间example的详细步骤。首先解决域索引问题，然后处理物化视图，并最终通过级联删除约束条件来成功移除表空间。

SYS@ PROD1>drop tablespace example including contents and datafiles;
drop tablespace example including contents and datafiles
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

SYS@ PROD1>select index_name, table_owner, table_name, tablespace_name from dba_indexes where index_type='DOMAIN';

INDEX_NAME TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ ---------- ------------------------------ ------------------------------
SUP_TEXT_IDX SH SUPPLEMENTARY_DEMOGRAPHICS

SYS@ PROD1>drop index sh.SUP_TEXT_IDX ;
Index dropped.

################################################
SYS@ PROD1>drop tablespace example including contents and datafiles;
drop tablespace example including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

SYS@ PROD1>select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
2 from dba_registered_snapshots
3 where name in (select table_name from dba_tables where tablespace_name ='EXAMPLE');
'DROPMATERIALIZEDVIEW'||OWNER||'.'||NAME||'PRESERVETABLE;'
--------------------------------------------------------------------------------
drop materialized view SH.CAL_MONTH_SALES_MV PRESERVE TABLE;
drop materialized view SH.FWEEK_PSCAT_SALES_MV PRESERVE TABLE;
SYS@ PROD1>drop materialized view SH.CAL_MONTH_SALES_MV PRESERVE TABLE;
Materialized view dropped.
SYS@ PROD1>drop materialized view SH.FWEEK_PSCAT_SALES_MV PRESERVE TABLE;
Materialized view dropped.

################################################

SYS@ PROD1>drop tablespace example including contents and datafiles;
drop tablespace example including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SYS@ PROD1>drop tablespace example including contents and datafiles cascade constraints;
Tablespace dropped.

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

转载于:http://blog.itpub.net/29653765/viewspace-1764083/