I had a case where the drop table with purge option DDL statement produce the error from the title and left partitioned table in the partially dropped state.

I can still see the table and all belonging objects like indexes/constraints… in dictionary, but If I try to select from that table, I get the following error:

ORA-14137: Table in partially dropped state, submit DROP TABLE PURGE
14137. 00000 – “Table in partially dropped state, submit DROP TABLE PURGE”
*Cause: An attempt was made to access a partitioned table in a partially
dropped state.
*Action: Submit DROP TABLE table_name PURGE to drop the table.

Additionally, table is in unusable status, as can be seen by executing the following statement:


select * from dba_tables where owner = 'OWNER' and table_name = 'TABLE_NAME';

Status: UNUSABLE

In Oracle MOS I can only find some bugs related to 10.2 version of database.

As I’m running 11.2, I shouldn’t be hit by reported bugs.

By googling some people report the same error when they execute


drop table table_name;

statement without purge option. By following those advice, the proper DDL should look like this one:


drop table table_name purge;

But, as you can see from the title, I’ve already execute drop table statement with included purge option.

If I try to execute:


drop table table_name purge;

statement again, I get the error:

ORA-00054: Resource Busy and acquire with NOWAIT.

I resolved the issue by setting session DDL lock timeout (new 11g feature) to 30 seconds:


alter session set ddl_lock_timeout = 30; 

After that I can finally drop the table successfully


drop table table_name purge;


Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.