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?



1 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.