删除表空间EXAMPLE错误ORA-29857处理办法

windjack

删除表空间错误一例处理办法

------------------------------------------------------------------------------------------------------------------

环境：

OS:CENTOS 7.8

DB:12.1.0.2

------------------------------------------------------------------------------------------------------------------

今天在测试库删除表空间EXAMPLE时，遇到错误，详情如下：

SQL> 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

删除表空间报错说存在域索引或者二级对象，导致无法删除表空间。

搜索域索引：

SQL> SELECT OWNER,INDEX_NAME, TABLE_OWNER, TABLE_NAME

FROM DBA_INDEXES WHERE INDEX_TYPE='DOMAIN'

AND TABLESPACE_NAME ='SAC';

no rows selected

并没有发现域索引。

官方说明：

The table which is in the tablespace to be dropped has a domain index which needs to be dropped before dropping the tablespace.

Domain indexes cannot be created in a specific tablespace and the TABLESPACE_NAME column in DBA_INDEXES is always null for domain indexes.

要删除的表空间中的表有一个域索引，这个域索引在删除表空间前需要被删除掉。

域索引不能被创建在指定的表空间，对于域索引，DBA_INDEXES中的TABLESPACE_NAME列值总是空值。

You need to identify and drop the secondary objects:

你需要找出并删除二级对象：

1.The domain index associated with a table in the tablespace to be dropped can be identified from the following query:

要删除的与在这个表空间中的表相关的域索引可以通过下面的查询找出来：

SQL> SELECT INDEX_NAME,I.TABLE_NAME,I.OWNER FROM DBA_INDEXES I, DBA_TABLES T

WHERE T.TABLE_NAME=I.TABLE_NAME

AND T.OWNER=I.OWNER

AND I.INDEX_TYPE='DOMAIN'

and t.TABLESPACE_NAME='EXAMPLE';

INDEX_NAME                     TABLE_NAME                     OWNER

------------------------------ ------------------------------ ------------------------------

SUP_TEXT_IDX                   SUPPLEMENTARY_DEMOGRAPHICS     SH

2.Secondary objects associated with domain indexes, can be identified from the following query:

与域索引相关的二级对象，可以通过下面的查询找出来：

SELECT SECONDARY_OBJECT_OWNER,SECONDARY_OBJECT_NAME,SECONDARY_OBJDATA_TYPE FROM DBA_SECONDARY_OBJECTS WHERE INDEX_NAME='SUP_TEXT_IDX';

SECONDARY_OBJECT_OWNER         SECONDARY_OBJECT_NAME          SECONDARY_OBJDATA_TY

------------------------------ ------------------------------ --------------------

SH                             DR$SUP_TEXT_IDX$N              FROM INDEXTYPE

SH                             DR$SUP_TEXT_IDX$R              FROM INDEXTYPE

SH                             DR$SUP_TEXT_IDX$K              FROM INDEXTYPE

SH                             DR$SUP_TEXT_IDX$I              FROM INDEXTYPE

Once you identify the secondary objects, you can drop those and then drop the tablespace.

一旦你找出这些二级对象，你就可以删除它们然后再删除表空间。

SQL> drop index sh.SUP_TEXT_IDX;

Index dropped.

再尝试删除表空间

SQL> 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

又报错误存在物化视图

接着查找物化视图：

SQL> SELECT a.OWNER,a.MVIEW_NAME FROM  DBA_MVIEW_ANALYSIS  a

OWNER                          MVIEW_NAME

------------------------------ ------------------------------

SH                             CAL_MONTH_SALES_MV

SH                             FWEEK_PSCAT_SALES_MV

删除物化视图

SQL> drop materialized view sh.CAL_MONTH_SALES_MV;

Materialized view dropped.

SQL> drop materialized view sh.FWEEK_PSCAT_SALES_MV;

Materialized view dropped.

再尝试删除表空间

SQL> 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

真是一波三折，又报错存在外键依赖，使用子句cascade constaints。

SQL> drop tablespace example including contents and datafiles cascade constraints;

Tablespace dropped.

终于删除成功了。