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

20-AUG-2010 in Oracle forum OTN, a interesting thread , here i documented it for future reference.

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

i thank the forum Opponent Phiri Lighton and participants especially nicolas,ogan and chinar here.

here, we will see the examples , What are the possiblities to get ORA-950 Error

Changes
Made changes to the TEMP tablespace setup.
1. Created a temporary tablespace called TEMP1.
2. Set this tablespace to become the default temporary tablespace.
3. Dropped the existing permanent TEMP tablespace.
4. Renamed the default temporary tablesapce TEMP1 to TEMP.
Reason for this problem/issue
There is an open unpublished bug where basic problem 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.
Solution
To resolve in this case, just alter the user to have the new temporary tablespace name, replacing the
_$deleted entry.
SQL> create temporary tablespace temp2 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create temporary tablespace temp3 tempfile '+GROUP01' size 20M;
Tablespace created.
SQL> create user testuser identified by testuser temporary tablespace temp2;
User created.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> alter tablespace temp3 rename to temp2;
Tablespace altered.
- Now look in ts$ and will see the _$deleted construct which includes the ts#.
SQL> select ts#, name from ts$;
TS# NAME
---------- ------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDO
6 MYDATA
7 MYINDEXES
8 TESTCASES
9 _$deleted$9$0
10 TEMP2
11 rows selected.
- And when you check the users temporary_tablespace, it too was changed to the _$deleted construct
which causes all the problems.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER _$deleted$9$0
- To resolve, just alter the user to a valid temporary tablespace.
SQL> alter user testuser temporary tablespace temp2;
User altered.
SQL> select username, temporary_tablespace
2 from dba_users where username = 'TESTUSER';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TESTUSER TEMP2
To avoid this problem altogether, until the bug is fixed, do not rename a tablespace to one that use to
exist on the database. For example, here would be steps to complete the temp tablespace change.
create tablespace NEWTEMP
... change everyone and default temporary tablespace NEWTEMP
drop tablespace TEMP ... exisiting one
create tablespace TEMP ... building it the way you want
.... flip everyone back and default temporary tablespace TEMP
drop tablespace NEWTEMP

reference: ORACLE OTN Forum, MetaLink