Friday, December 4, 2009

ORA-00959: tablespace '_$deleted$0$0' does not exist

If this error occurs when you try to add a partition on partition table then all the table partitions had once been moved to another tablespace and the original tablespace has now been dropped or new tablespace where partitons now reside has been renamed to the original tablespace name.

Issue

alter table [table_name] modify default attributes tablespace [tablespace name];

1 comment:

  1. I've experienced this painful problem.
    It can be difficult to drive out. Even if you rename the default attributes, an index or table partition associated with the parent object might have the tablespace name problem.

    What's worse is that in some views it is not seen, but in others it can be. Try running DBMS_METADATA package to get the ddl for your tables and dependent indexes to see if this has happened to you. Each segment has to have its tablespace changed to get out of this tar pit.

    This has ramifications for recovery as well -- exports with the "deleted" names can work fine, but on import, you get ORA-00959.

    This bug was new in 10gR2 and present in 11gR1.

    ReplyDelete