Home » RDBMS Server » Server Administration » ORA-00959: While dropping tablespace. (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit )
ORA-00959: While dropping tablespace. [message #655268] Thu, 25 August 2016 23:40 Go to next message
Manoj.Gupta.91
Messages: 231
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
Re: ORA-00959: While dropping tablespace. [message #655275 is a reply to message #655268] Fri, 26 August 2016 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: ORA-00959: While dropping tablespace. [message #655286 is a reply to message #655275] Fri, 26 August 2016 02:04 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 231
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
Re: ORA-00959: While dropping tablespace. [message #655288 is a reply to message #655286] Fri, 26 August 2016 02:09 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Which database triggers I need to check?
All of them.

Quote:
What to do exactly?
Analyze trigger_body.

Quote:
Whatever information I could found, it is Oracle's bug.
If you think then your only choice is to raise a SR.

Previous Topic: how can open database when database already shutdown
Next Topic: Login in DATABASE From Server
Goto Forum:
  


Current Time: Sun Oct 21 00:35:45 CDT 2018