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;
Comments