Home » RDBMS Server » Server Administration » Unable to drop tablespace (10g)
Unable to drop tablespace [message #562678] Fri, 03 August 2012 06:19 Go to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
I am getting the following errors when I try drop a tablespace.
I already did the following.

a) The tablespace & its datafiles offline.
b) I have purged dba_recyclebin.


SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Report message to a moderator

Re: Unable to drop tablespace [message #562683 is a reply to message #562678] Fri, 03 August 2012 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you put the tablespace offline then the object in the recycle bin are not purged.
The tablespace MUST be online to be able to purge the associated recycle bin objects.

Regards
Michel

Report message to a moderator

Re: Unable to drop tablespace [message #562734 is a reply to message #562678] Sat, 04 August 2012 00:11 Go to previous messageGo to next message
GirishSharma
Messages: 22
Registered: March 2010
Junior Member
As Michel has explained, first you have to purge recyclebin first then offline tablespace and then drop tablespace. So, now bring online the tablespace, purge dba_recyclebin; offline the tablespace and then drop tablespace.

If you still get the problem, let us know.

Regards
Girish Sharma

Report message to a moderator

Re: Unable to drop tablespace [message #562877 is a reply to message #562734] Mon, 06 August 2012 09:37 Go to previous messageGo to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
No..Still the problem persist.

Report message to a moderator

Re: Unable to drop tablespace [message #562880 is a reply to message #562877] Mon, 06 August 2012 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


38301, 00000, "can not perform DDL/DML over objects in Recycle Bin"
// *Cause: Tried to perform DDL or DML operation on Recycle Bin object.
// *Action: DDL or DML operations are not permitted on Recycle Bin objects.

[Updated on: Mon, 06 August 2012 09:55]

Report message to a moderator

Re: Unable to drop tablespace [message #562887 is a reply to message #562880] Mon, 06 August 2012 11:47 Go to previous messageGo to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> alter tablespace db_maintenance offline;

Tablespace altered.

SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Report message to a moderator

Re: Unable to drop tablespace [message #562890 is a reply to message #562887] Mon, 06 August 2012 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Connect as a DBA (not SYS/SYSDBA) and execute:
alter tablespace db_maintenance online;
purge tablespace db_maintenance;
drop tablespace db_maintenance including contents and datafiles;

Regards
Michel

Report message to a moderator

Re: Unable to drop tablespace [message #562987 is a reply to message #562890] Tue, 07 August 2012 11:25 Go to previous messageGo to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
SQL> alter tablespace db_maintenance online;

Tablespace altered.

SQL> purge tablespace db_maintenance;

Tablespace purged.

SQL> drop tablespace db_maintenance including contents and datafiles;
drop tablespace db_maintenance including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

Report message to a moderator

Re: Unable to drop tablespace [message #562990 is a reply to message #562987] Tue, 07 August 2012 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I think you're in a bug that existed in 10.2.0.1, post the result of:
select owner, segment_name, partition_name, segment_type 
from dba_segments 
where tablespace_name='DB_MAINTENANCE'
  and segment_name like 'BIN$%'
  and (owner, segment_name) not in (select owner, object_name from dba_recyclebin)
order by 1, 2, 3
/

Regards
Michel

[Updated on: Tue, 07 August 2012 11:46]

Report message to a moderator

Re: Unable to drop tablespace [message #562997 is a reply to message #562990] Tue, 07 August 2012 12:59 Go to previous messageGo to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
yes. I see certain objects for the tablespace in segments view

SQL> select SEGMENT_NAME,SEGMENT_TYPE,owner from dba_segments where tablespace_name='DB_MAINTENANCE';

SEGMENT_NAME SEGMENT_TYPE OWNER
--------------------------------------------------------------------------------- ------------------ ------------------------------
BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0 TABLE ORACLE
BIN$Pks790fcQEzgQ8+Ct11ATA==$0 TABLE ORACLE
105.132 SPACE HEADER SYS

However when i try to drop those i am getting the same error.

Report message to a moderator

Re: Unable to drop tablespace [message #562999 is a reply to message #562997] Tue, 07 August 2012 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is NOT the query I asked, it was just simple to copy and paste it, why don't you do it? It is a very rude attitude towards someone that tries to help you for days.

Regards
Michel

Report message to a moderator

Re: Unable to drop tablespace [message #563059 is a reply to message #562999] Wed, 08 August 2012 03:27 Go to previous messageGo to next message
hemchander83@gmail.com
Messages: 10
Registered: July 2012
Location: US
Junior Member
Hi Michel,

Sorry for it... Please find the requested output.

select owner, segment_name, partition_name, segment_type
from dba_segments
where tablespace_name='DB_MAINTENANCE'
and segment_name like 'BIN$%'
and (owner, segment_name) not in (select owner, object_name from dba_recyclebin)
order by 1, 2, 3
/

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------------------ ------------------
ORACLE BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0 TABLE
ORACLE BIN$Pks790fcQEzgQ8+Ct11ATA==$0 TABLE

Report message to a moderator

Re: Unable to drop tablespace [message #563061 is a reply to message #563059] Wed, 08 August 2012 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So you are in the bug.
And, sorry, I can't remember the workaround which was updates in dictionary.
Check MOS/Metalink for the workaround or even open a SR to Oracle and you will have a supported answer.

Regards
Michel

Report message to a moderator

Re: Unable to drop tablespace [message #563062 is a reply to message #563061] Wed, 08 August 2012 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post the result of:
select dropped 
from dba_tables
where table_name in ('BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0','BIN$Pks790fcQEzgQ8+Ct11ATA==$0')
/


And, Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Unable to drop tablespace [message #563064 is a reply to message #563062] Wed, 08 August 2012 03:54 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I have an idea, not sure it works but try it:
alter session set recyclebin=OFF;
drop table "BIN$Pks1AnxmMCTgQ8+Ct10wJA==$0";
drop table "BIN$Pks790fcQEzgQ8+Ct11ATA==$0";
alter session set recyclebin=ON;
drop tablespace...

Regards
Michel

Report message to a moderator

Previous Topic: Few Cluster services are down.Why
Next Topic: Unable to proceed
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 02:25:41 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.