Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error on "purge recyclebin"

707809Jan 4 2011 — edited Jan 5 2011
Hi,

I am using Oracle 10g (10.2.0.2.0).
I am having problems purging the recyclebin, when connected as dbadmin.

When I tried to do a purge recyclebin, I was left with only 2 objects in the recyclebin, and got an error -

SQL> purge recyclebin;
purge recyclebin
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02449: unique/primary keys in table referenced by foreign keys

The objects in the database are:

SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, ts_name
from recyclebin;

object_name original_name type can_undrop can_purge droptime ts_name
=========================================================================================
SYS_IL0000011599C00063$$ SYS_IL0000011599C00063$$ LOB INDEX NO NO 2010-10-08:09:13:34 small_tables
SYS_LOB0000011599C00063$$ SYS_LOB0000011599C00063$$ LOB NO NO 2010-10-08:09:13:34 small_tables

If I try connecting as sysdba, and purging the dba_recyclebin, it says purged -

sqlplus as sysdba

SQL> purge dba_recyclebin;
DBA Recyclebin purged.

But, I still see the objects in the recyclebin. Doing a purge tablespace gives me the same error -

SQL> purge tablespace small_tables;
purge tablespace small_tables
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02449: unique/primary keys in table referenced by foreign keys

Any help would be greatly appreciated.

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 2 2011
Added on Jan 4 2011
3 comments
893 views