ORA-38301: can not perform DDL/DML over objects in Recycle Bin

[Jason]

Hi Erman we are facing a serious issue,in alert log we are seeing "performing DML/DDL operation over object in bin".And clients are facing different issues like ;

ORA-04045: errors during recompilation/revalidation of APPS.BIN$QyQ/t68DfB3gU4xyqMANqA==$0
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ORA-06512: at "APPS.PO_HEADERS_PKG_S0", line 617

--------------------------------------------------------------
FRM-40735: POST-FORMS-COMMIT trigger raised unhandled exception ORA-04045.

[ErmanArslansOracleBlog]

Did you drop an object recently?

 An object that is supposed to be used by PO_HEARDS_PKG_SO is dropped and it is in recycle bin now.

Check recycle bin. Look at ORIGINAL NAME    RECYCLEBIN NAME   columns.

See what is the original name of the recycle bin object named APPS.BIN$QyQ/t68DfB3gU4xyqMANqA==$0 ..

This may be a bug..

Send me the name of that object.. Then we ll see what to do next..

Our options are:
1)purge recycle bin..
2)take that object back from the recycle bin.

[Jason]

object name is XXPO_KN_EARN_SS_UPD which is APPS.BIN$QyQ/t68DfB3gU4xyqMANqA==$0(this is trigger)
but WE PURGED that and also we purged dba_recycle but useless still we are seeing in recycle.

No we didnt drop that but in 8 Dec someone did.We created trigger again from our TEST database ,but users still getting same errors,and we are seeing same error in alertlog

[ErmanArslansOracleBlog]

alter system set recyclebin=off;
drop that object
Retry your failing operation...

Then alter system set recyclebin=on;

If the issue persists; do the following;

1) sqlplus / as sysdba
2) ALTER SYSTEM SET recyclebin = OFF DEFERRED;
3) disconnect and exit sqlplus
4) sqlplus / as sysdba
5) drop THAT OBJECT
6(RETRY THE FAiling operation
7) sqlplus / as sysdba
8) ALTER SYSTEM SET recyclebin = ON DEFERRED; or ALTER SYSTEM SET recyclebin = ON;

[Jason]

ty problem solved but i didnt get that.related file is still exist in recylcebin.I guess restart database solved the problem.
how can we update can_undrop and can_pruge columns in recyclebin?

[ErmanArslansOracleBlog]

You purge dba_recyclebin but the object is still there right?
IF so I m sending you the action plan:

1) sqlplus / as sysdba
2) purge dba_recyclebin;
3) ALTER SYSTEM SET recyclebin = OFF;
4) ALTER SYSTEM SET recyclebin = ON;

If the issue persist, send me the outputs.. (query outputs, command outputs and everyting) for further analysis.

[ErmanArslansOracleBlog]

what you gonna do with that columns?

If you wanna purge a table from recyclebin; you issue the following command;

purge table table_name;

[ErmanArslansOracleBlog]

Read the following QA:

Why do objects owned by SYS show "no" in the CAN_PURGE column of dba_recyclebin?

This means that the particular object cannot be purged individually; e.g.,  using the 'PURGE
TABLE' or 'PURGE INDEX' syntax.  The 'PURGE DBA_RECYCLEBIN' command will  purge it, along with all the
dependent objects.

If these objects were purged, would those that have CAN_PURGE values of  "no" purge or be bypassed?
These objects will be purged when parent objects are purged.

Is there an option for a WHERE clause when running the 'PURGE DBA_RECYCLEBIN' command to code for CAN_PURGE='yes'?

No, there is not an option.  The command 'PURGE DBA_RECYCLEBIN' should purge all parent objects and its dependents.

By purging using the command 'PURGE DBA_RECYCLEBIN' on the objects that have can_purge="no", will this cause problems with any object in the database?

No. PURGE DBA_RECYCLEBIN won't cause problems with any objects in the database.