Oracle: How to drop tablespace with missing file with ORA-29857

Product: Oracle DB

Version: 19.2 - 21c

This procedure illustrate how to drop a tablespace which has dbf files that missing, or corrupted.

Assumption:

• Recycle bin is active
• Several users has table and index within the affected tablespace
• Entire tablespace will drop, even if one of the multiple dbf file missing
• Tablespace name is ABC

Problems simulation:

SQL> drop tablespace ABC including contents and datafiles;

ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

Workaround Procedure:

1. Find out all the dbf file ID of the tablespace. Note down the FILE_ID to use later

select file_id from dba_data_files where tablespace_name = 'ABC';

2. Disable all the above dbf file. If multiple files, execute these once per file

alter database datafile [file_id] offline drop;

3. Disable recycle bin

alter system set recyclebin=off deferred;

4. Drop tablespace with all its dbf file

drop tablespace ABC including contents and datafiles;

5. Verify dbf file dropped. There should be no output

select file_id from dba_data_files where tablespace_name = 'ABC';

6. Re-enable recycle bin

alter system set recyclebin=on deferred;