Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hariharan.

Asked: November 10, 2016 - 9:30 am UTC

Last updated: November 28, 2016 - 10:48 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

On one of our 11g databases we are not able to purge an object from the recyclebin:

sys@DEVDB11:SQL> select count(*) from dba_recyclebin;

COUNT(*)
--------------
1320

sys@DEVDB11:SQL> purge dba_recyclebin;

DBA Recyclebin purged.

sys@DEVDB11:SQL> select count(*) from dba_recyclebin;

COUNT(*)
--------------
1

sys@DEVDB11:SQL> select * from dba_Recyclebin;

OWNER OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ ------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME DROPTIME DROPSCN
------------------------- ------------------------------ ------------------- ------------------- --------------
PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE
-------------------------------- --- --- -------------- -------------- -------------- --------------
RAISINTST TRG_INVENTORY_C TRG_INVENTORY_C DROP
TRIGGER 2014-06-17:11:18:48 2016-08-25:14:13:16 6939042386247
NO NO 2847547 7095143 2847547


Also Tried:
++++++++++++

1. Get the DDL of the Trigger :

Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TRIGGER','<TRI_NAME>','<OWNER>) from dual;


2. Drop the Trigger

3. Now Purge Recyclebin

4. Make sure that Trigger Does not exist in DB / Recyclebin

5. Recreate trigger using the DDL




Could you please advise on this issue ?


Thanks,

and Chris said...

MOS note 1595081.1 describes this issue and discusses some workarounds:

Normal a table and all of its dependent objects (such as indexes, triggers) will be purge after perform purge command. However in some abnormal cases, running purge command return successful, but some dependent objects (such as indexes, triggers) cannot be purge properly.

For example:

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> select object_name,original_name,type from dba_recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------- -------------------- ----------
BIN$4UFrbTF+BvXgRAAQ4A6lLg==$0 trigger90 TRIGGER
BIN$4UFrbTF9BvXgRAAQ4A6lLg==$0 trigger80 TRIGGER
BIN$4UFrbTF8BvXgRAAQ4A6lLg==$0 trigger75 TRIGGER
BIN$4UFrbTF7BvXgRAAQ4A6lLg==$0 trigger91 TRIGGER
BIN$4UFrbTF6BvXgRAAQ4A6lLg==$0 trigger81 TRIGGER
BIN$4UFrbTF5BvXgRAAQ4A6lLg==$0 trigger76 TRIGGER
BIN$4UFrbTF2BvXgRAAQ4A6lLg==$0 INDEX1 INDEX
BIN$4UFrbTF1BvXgRAAQ4A6lLg==$0 INDEX2 INDEX
BIN$4UFrbTF0BvXgRAAQ4A6lLg==$0 INDEX3 INDEX
BIN$4UFrbTFzBvXgRAAQ4A6lLg==$0 INDEX4 INDEX

SOLUTION

Please try to perform the following action plan to workaround this issue:

1. connect as sysdba user :
SQL> purge dba_recyclebin;

or

2. connect as that particular schema 'owner':
SQL> conn <Username>/<Password>
SQL> purge recyclebin;

or

3. disable recyclebin and try to purge again:
SQL> conn / as sysdba
SQL> alter system set recyclebin=off;
SQL> purge dba_recyclebin;
SQL> alter system set recyclebin=on;

or

4. re-drop the parent table, then the parent table will present in recyclebin and purge recyclebin again.
Note, for production database, please backup table before drop!
SQL> drop table <table_name>;
SQL> purge recyclebin;

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 28, 2016 - 8:42 am UTC

WE have completed all the options and nothing worked.. what we realized was that the information of the SQL to re-generate the TABLE as provided by DBMS_METADATA.GET_DDL contained NOT NULL constraint information that was tagged with "BIN$....." names...

When trying to drop the table we got ORA-00600: internal error code, arguments: [kglidinsi1] .After checking support site, we did DROP TABLE xxxx CASCADE CONSTRAINTS PURGE to avoid the table to finish in the recyclebin.

After that we re-create the table with new NOT NULL constraints (removing the "BIN$.." tags). This time the normal DROP TABLE did work and then we PURGE DBA_RECYCLEBIN, and although it returns with no errors, in the recycle bin we have now several rows with the same TRIGGER as before

Before the test we had 1 row that we could not purge related to a trigger, now we have five. Not nice.
Chris Saxon
November 28, 2016 - 10:48 am UTC

I think you're going to have to take this one up with support.

But what exactly did you do? I'm not sure how the not null constraint comes in here?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library