Why Does a Bunch of Invalid Objects with Garbage Names Appear in Oracle

Prior to Oracle 10g a DROP command permanently removed objects from the database. In Oracle 10g a DROP command places an object to the recycle bin. The extents allocated to the segment will not be reallocated until you purge the object. You can restore the object from the recycle bin at any time.

A recycle bin contains all the dropped database objects, until

• You permanently drop them with the PURGE command.
• Recover the dropped objects with the UNDROP command.
• There is no room in the tablespace for new rows or updates to existing rows.
• The tablespace needs to be extended.

The usage of recycle bin can be enables/disabled this way:

ALTER SESSION SET recyclebin = ON/OFF; -- for the current session
ALTER SYSTEM SET recyclebin = ON/OFF;  -- for all sessions

You can view the dropped objects in the recycle bin from two dictionary views:

• user_recyclebin - lists all dropped user objects
• dba_recyclebin - lists all dropped system-wide objects

For example you drop a table:

DROP TABLE drop_test;

Now you see the table with a strange name like “BIN$njeSgciWSy+6io3ZQS+Bug==$0”

It is the same table but with a new name. You even can see its contents using:

SELECT * FROM "BIN$njeSgciWSy+6io3ZQS+Bug==$0"

To reinstate the table please use this command:

FLASHBACK TABLE drop_test TO BEFORE DROP;

Now you can see the latest version of the table again.

If you want to drop table permanently you should use:

DROP TABLE drop_test PURGE;

And to empty all users recycle bin please use the following command:

PURGE RECYCLEBIN;

NOTE: All triggers for the dropped table are also placed into recycle bin and their names are changed in the same way.

Ispirer Home Page Database Migration Application Conversion Downloads