OraFAQ Forum: Server Administration » ORA-00959: While dropping tablespace.

Home » RDBMS Server » Server Administration » ORA-00959: While dropping tablespace. (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00959: While dropping tablespace. [message #655268]

Thu, 25 August 2016 23:40

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi All,

I'm facing an error while trying to drop a tablespace

SQL> DROP TABLESPACE USER_TRANS INCLUDING CONTENTS AND DATAFILES ;
DROP TABLESPACE USER_TRANS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00959: tablespace '_$deleted$9$0' does not exist


SQL>

I've tried below options.
(1) Restarted DB.
(2) Moved all objects from this tablespace to another tablespace. (Currently there is no object in this tablespace).

I can see to deleted entries in TS$

select * from ts$ where name like '%deleted%';

TS#	NAME	OWNER#	ONLINE$	CONTENTS$	UNDOFILE#	UNDOBLOCK#	BLOCKSIZE	INC#	SCNWRP	SCNBAS	DFLMINEXT	DFLMAXEXT	DFLINIT	DFLINCR	DFLMINLEN	DFLEXTPCT	DFLOGGING	AFFSTRENGTH	BITMAPPED	PLUGGED	DIRECTALLOWED	FLAGS	PITRSCNWRP	PITRSCNBAS	OWNERINSTANCE	BACKUPOWNER	GROUPNAME	SPARE1	SPARE2	SPARE3	SPARE4
3	_$deleted$3$0	0	3	0	0	0	8192	1	0	0	1	2147483645	128	128	128	0	0	2147483645	128	0	1	4098	0	3778				0	0		
9	_$deleted$9$0	0	3	0	0	0	8192	1	0	0	1	2147483645	8	128	8	0	1	2147483645	8	0	1	4129	0	1361918				0	0

Please suggest how to proceed.

Thanks & Regards
Manoj

Report message to a moderator

Re: ORA-00959: While dropping tablespace. [message #655275 is a reply to message #655268]

Fri, 26 August 2016 00:57

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In these cases, the first thing to do is search for a database trigger.

Report message to a moderator

Re: ORA-00959: While dropping tablespace. [message #655286 is a reply to message #655275]

Fri, 26 August 2016 02:04

Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi

Senior Member

Hi Michel,

Which database triggers I need to check?
What to do exactly?

Whatever information I could found, it is Oracle's bug.

But don't know how to resolve this.

Regards
Manoj

Report message to a moderator