Delete tablespace hints ORA-00604 and ORA-38301 in Oracle

Today, we need to make room on this machine, found that Oracle has a DBF file accounted for about 30G, the data file corresponding to the user usually rarely used, and then want to handle it-delete table space operations.

The native database is a 32-bit 10.2.0.1.0, and table space has several table data for TEST_TB. Start with the DBA role of the system user, directly execute

Drop tablespace TEST_TB including contents and datafiles;

Tips:

ORA-00604: A recursive SQL Level 1 error occurred

ORA-38301: Unable to perform ddl/dml on objects in the Recycle Bin

Cause: The table space TEST_TB data exists in dba_segments, and queries can be executed by the DBA User:

Select Segment_name,segment_type,owner from dba_segments where tablespace_name= ' TEST_TB ';

Processing method:

1. Log in with the corresponding user for the table space

2. Execute purge recyclebyin;

3. Re-executes drop tablespace TEST_TB incluing contents and datafiles;

   After execution, the DBF file does not exist and space is freed up.

Delete tablespace hints ORA-00604 and ORA-38301 in Oracle