Home » RDBMS Server » Server Administration » fail to drop tablespace (oracle 10g, AS4)
fail to drop tablespace [message #276187] Wed, 24 October 2007 05:43 Go to next message
Messages: 27
Registered: February 2006
Junior Member
i drop the table having size around 10gb
SQL>drop table test;
and try resize tablesapce userdata ,which having only this object.It is fail to do so,as that table is goes to recyclebin
then i try to purge recycle bin
SQL>purge recyclebin
then error comes
ORA-00604: error occurred at recursive SQL level 2
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 16
then i try to create new temp. tables in same tablespace userdata.after this there is no table in recycle bin
but when try to drop a tablesapce userdata it gives
SQL>drop tablespace userdata including contents and datafile;it gives error
ORA-01561: failed to remove all objects in the tablespace specified

i check all view(dba_segments,dba_extents) w.r.to this tablespace
there is no objects in the tablespace.

please tell what really happen with that tablesapce?
can i able to release 10gb size?


Re: fail to drop tablespace [message #276195 is a reply to message #276187] Wed, 24 October 2007 06:07 Go to previous messageGo to next message
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

May be there is a corruption in your data dictionary.
Re: fail to drop tablespace [message #276196 is a reply to message #276187] Wed, 24 October 2007 06:10 Go to previous message
Michel Cadot
Messages: 65143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you have any trigger on DDL.
Check the code of this trigegr.
Check if then there is any implicit conversion (for instance on date).

Previous Topic: Instance terminate by lgwr process - error 470
Next Topic: AWR and statspack usage on Oracle 10g Database
Goto Forum:

Current Time: Sat Aug 19 06:29:05 CDT 2017

Total time taken to generate the page: 0.18794 seconds