Oracle tablespace deletion Problems and Solutions

Oracle tablespace deletion Problems and Solutions
Problem 1: An error ORA-29857 was reported during tablespace Deletion

Table space deletion statement:DROP TABLESPACE SAC INCLUDING CONTENTS AND DATAFILES;
According to the MOS document:
How To Resolve ORA-29857 During a Drop Tablespace although No Domain Index exists in This Tablespace (Document ID 1610456.1)
For the ORA-29857 error, the document says clearly:

Symptom:

An error ORA-29857, for example:
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

However, you did not 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

Cause:

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.

The table in the tablespace to be deleted has a domain index, which must be deleted before being deleted.
The domain index cannot be created in the specified tablespace. For the domain index, the value of the TABLESPACE_NAME column in DBA_INDEXES is always null.

Solution:

You need to identify and drop the secondary objects:
You need to find and delete the second-level object:

1. The domain index associated with a table in the tablespace to be dropped can be identified from the following query:
The domain indexes related to the tables in the tablespace to be deleted can be found through 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.OWNERAND I.INDEX_TYPE='DOMAIN'and t.TABLESPACE_NAME='&TABLESPACE_NAME';

2. Secondary objects associated with domain indexes, can be identified from the following query:
Secondary objects related to the domain index can be found through 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.
Once you find these second-level objects, you can delete them and then delete the tablespace.

Please see the following example:
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 indexesSQL> 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 nullSQL> 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 a30SQL> col INDEX_NAME for a30SQL> 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 SACSQL> 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 selectedSQL> DROP TABLESPACE SAC INCLUDING CONTENTS AND DATAFILES;Tablespace dropped.

Problem 2: ORA-02429 encountered during tablespace Deletion

The description of the MOS document is also clear about the error of ORA-02429:
Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (Document ID 1918060.1)

Symptom:

Table space deletion failed with the following error:

SQL> DROP TABLESPACE REP_DATA INCLUDING CONTENTS AND DATAFILES;DROP TABLESPACE REP_DATA INCLUDING CONTENTS AND DATAFILES*ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-02429: cannot drop index used for enforcement of unique/primary key

Solution:

Find the constraint name for the unique/primary key, disable the constraint and drop the tablespace again.
Find the unique/primary key constraint names, disable these constraints, and then delete the tablespace again.

Steps: ==== 1) Execute below query to find the constraint name: Execute the following query to find the constraint name: SQL> select owner, constraint_name, table_name, index_owner, index_namefrom dba_constraintswhere (index_owner, index_name) in (select owner, index_name from dba_indexeswhere tablespace_name = '<tablespace_name>'); 2) Disable the constraint: Disable constraints: SQL> alter table <table_name> disable constraint <constraint_name>; 3) Drop the tablespace: delete a TABLESPACE: SQL> DROP tablespace <tablespace_name> INCLUDING CONTENTS AND DATAFILES;

Problem 3: The tablespace is deleted and the host disk space is not released.

If you haven't released it for a long time, refer:

The recommended operations are as follows:
1. Download an lsof software and install it on google.
2. Find the process in which the file is being deleted
Lsof | grep deleted
3. kill the corresponding process space and release it.

In this case, it is not recommended to restart the database or host.