How to delete tablespace and table after delete datafiles?

Question

I deleted datafiles with asmcmd. I need to delete tablespace and tables on this tablespace. I tried:

drop tablespace tablespace_name including contains cascade constraints

getting error:

ORA-14405: partitioned index contains partitions in a different tablespace

When I tried drop table, index or partition, getting this event and do nothing.

optimizer stats update retry.

How can I drop tablespace and all tables on this tablespace easily?

Thanks.

(Oracle 11.2.0.4.0)

Answer 1

According to the My Oracle Support documents, you might have hit Bug 19579109 : DROP TABLESPACE HANG ON RECURSIVE SQL.

Possible workaround would be to make sure that all paritions have statistics gathered before the drop table or set event 14534 before drop command as shown below.

alter session set events '14534 trace name context forever, level 1';
drop table <table_name>;

Refer to the MOS document for details.

Reference: Drop Tablespace Command Slow or Hanging (Doc ID 2060324.1)