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 the object in the recycle bin. The extents allocated to the segment are not 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 turned on/off 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 your table:

DROP TABLE drop_test;

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

It is your table but with the new name. You even can see it content 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 your table again.

If you want to drop table permanently you should use:

DROP TABLE drop_test PURGE;

And to empty all users recycle bin command

PURGE RECYCLEBIN;

should be used.

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