Friday, June 4, 2010

ORA-959 Tablespace '_$deleted$0$0' Does Not Exist or users has '_$deleted$0$0' as default tablespace.

You definitely have seen this if you use OEM tablespace reorganize feature using rename tablespace method.

The case is that a tablespace is renamed to one that use to exist on the database. Here are the steps that will cause this problem.

create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa

Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is introduced. The change is not taken care of properly, and is flushed to other dictionary tables causing this problem.

It also counts for temporary tbs.

Confirmed on to and


1) Apply Patch 5769963

2) Using SQL

Alter the user to have as default/temp tablespace the new tbs
If an object is offended such and index partition or table partition default tbs then alter the object.

Oracle Metalink Notes 604648.1, 789023.1

No comments:

Post a Comment