问题1：删除表空间期间遭遇报错 ORA-29857
删除表空间语句：DROP TABLESPACE SAC INCLUDING CONTENTS AND DATAFILES;
根据MOS文档：
How To Resolve ORA-29857 During a Drop Tablespace although No Domain Index exists in This Tablespace (文档 ID 1610456.1)
对于ORA-29857这个错误，文档说的很清楚：
现象：
删除表空间时，遇到报错ORA-29857，例如：
SQL> drop tablespace SAC including contents and datafiles
drop tablespace SAC
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
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=
'&TABLESPACE_NAME';
2.Secondary objects associated with domain indexes, can be identified from the following query:
与域索引相关的二级对象，可以通过下面的查询找出来：
SQL>
SELECT SECONDARY_OBJECT_OWNER,SECONDARY_OBJECT_NAME,SECONDARY_OBJDATA_TYPE
FROM DBA_SECONDARY_OBJECTS
WHERE INDEX_NAME=
'INDEX_NAME_From_Previous_Query';
Once you identify the secondary objects, you can drop those and then drop the tablespace.
一旦你找出这些二级对象，你就可以删除它们然后再删除表空间。
Please see the following example:
请看下面的例子：
SQL> CREATE TABLESPACE SAC DATAFILE
'C:\SAC.DBF' SIZE 50M;
Tablespace created.
SQL> CREATE TABLE SAC TABLESPACE SAC
AS SELECT *
FROM ALL_OBJECTS;
Table created.
SQL> begin
ctx_ddl.create_preference(
'SUBSTRING_PREF','BASIC_WORDLIST');
ctx_ddl.set_attribute(
'SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
end;
/
PL/SQL procedure successfully completed.
-- Trying
to create the domain index
in specific tablespace fails
with ORA
-29850:
SQL> CREATE INDEX SAC_INDX
ON SAC(OBJECT_TYPE) INDEXTYPE
IS CTXSYS.CONTEXT PARAMETERS (
'WORDLIST SUBSTRING_PREF MEMORY 50M') TABLESPACE SAC;
CREATE INDEX SAC_INDX
ON SAC(OBJECT_TYPE) INDEXTYPE
IS CTXSYS.CONTEXT PARAMETERS (
'WORDLIST SUBSTRING_PREF MEMORY 50M') TABLESPACE SAC
*
ERROR at line
1:
ORA
-29850: invalid
option for creation
of domain indexes
SQL> CREATE INDEX SAC_INDX
ON SAC(OBJECT_TYPE) INDEXTYPE
IS CTXSYS.CONTEXT PARAMETERS (
'WORDLIST SUBSTRING_PREF MEMORY 50M');
Index created.
SQL> drop tablespace sac including contents
and datafiles;
drop tablespace sac including contents
and datafiles
*
ERROR at line
1:
ORA
-29857: domain indexes
and/
or secondary objects exist
in the tablespace
-- Trying
to find the domain index
in this tablespace:
SQL>
SELECT OWNER,INDEX_NAME, TABLE_OWNER, TABLE_NAME
FROM DBA_INDEXES
WHERE INDEX_TYPE=
'DOMAIN'
AND TABLESPACE_NAME =
'SAC';
no rows selected
--Trying
to find segments created
in this newly created tablespace:
SQL>
SELECT SEGMENT_NAME,SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME=
'SAC';
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
SAC TABLE
-- Trying
to find the segment
for index SAC_INDX :
SQL>
SELECT TABLESPACE_NAME
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME=
'SAC_INDX';
no rows selected
-- Trying
to find the tablespace
for index SAC_INDX
from DBA_INDEXES :
SQL>
set null null
SQL>
select INDEX_TYPE,TABLE_TYPE,DOMIDX_STATUS,DOMIDX_OPSTATUS,SEGMENT_CREATED,TABLESPACE_NAME
from DBA_INDEXES
where INDEX_NAME=
'SAC_INDX';
INDEX_TYPE TABLE_TYPE DOMIDX_STATU DOMIDX SEG TABLESPACE_NAME
--------------------------- ----------- ------------ ------ --- ------------------------------
DOMAIN TABLE VALID VALID YES null
--
To find the indexes that are causing ORA
-29857 , please use the following query :
SQL> col TABLE_NAME
for a30
SQL> col INDEX_NAME
for a30
SQL>
SELECT INDEX_NAME,I.TABLE_NAME
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=
'SAC';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
SAC_INDX SAC
SQL> DROP INDEX SAC_INDX;
Index dropped.
--confirm that no secondary objects associated
with domain index still exist:
SQL>
SELECT SECONDARY_OBJECT_OWNER,SECONDARY_OBJECT_NAME,SECONDARY_OBJDATA_TYPE
FROM DBA_SECONDARY_OBJECTS
WHERE INDEX_NAME=
'SAC_INDX';
no rows selected
SQL> DROP TABLESPACE SAC INCLUDING CONTENTS
AND DATAFILES;
Tablespace dropped.
问题2：删除表空间期间遭遇 ORA-02429
