The Oracle Database recycle bin is a logical container that stores dropped database objects such as tables and indexes. When you drop a table or index, Oracle does not immediately remove the object from the database. Instead, it renames the object and places it in the recycle bin, allowing for potential recovery through the FLASHBACK TABLE statement.

When objects are dropped, they are renamed with a system-generated name that begins with "BIN$" followed by a unique identifier. The original object name and metadata are preserved in the recycle bin views for reference and recovery purposes.

The recycle bin structure contains the following key columns:

desc recyclebin;

Name           NULL     Type
-------------- -------- ------------
OBJECT_NAME    NOT NULL VARCHAR2(30)
ORIGINAL_NAME           VARCHAR2(32)
OPERATION               VARCHAR2(9)
TYPE                    VARCHAR2(25)
TS_NAME                 VARCHAR2(30)
CREATETIME              VARCHAR2(19)
DROPTIME                VARCHAR2(19)
DROPSCN                 NUMBER
PARTITION_NAME          VARCHAR2(32)
CAN_UNDROP              VARCHAR2(3)
CAN_PURGE               VARCHAR2(3)
RELATED        NOT NULL NUMBER
BASE_OBJECT    NOT NULL NUMBER
PURGE_OBJECT   NOT NULL NUMBER
SPACE                   NUMBER

The OBJECT_NAME column contains the system-generated name, while ORIGINAL_NAME stores the original object name.
The CAN_UNDROP column indicates whether the object can be restored using FLASHBACK TABLE.
The CAN_PURGE column shows if the object can be permanently removed.

Oracle provides several views to examine objects in the recycle bin. These views allow you to see what objects are available for recovery and their current status.

To view all deleted objects for the current user:

SELECT * FROM user_recyclebin;

This view has a synonym: recyclebin, which provides the same information and can be used interchangeably.

To view all deleted objects across the entire database (requires DBA privileges):

SELECT * FROM dba_recyclebin;

To see specific columns that provide the most useful information about dropped objects:

SELECT object_name, original_name, type, ts_name, droptime, can_undrop
FROM user_recyclebin;

This query shows the system-generated name, original name, object type, tablespace, drop time, and whether the object can be restored.

Purging removes objects permanently from the recycle bin, freeing up the space they occupied. Once purged, objects cannot be recovered using FLASHBACK TABLE. You can purge individual objects, entire tablespaces, or the entire recycle bin.

Syntax:

PURGE
{ TABLE table_name | INDEX index_name }
| TABLESPACE tablespace_name [ USER user_name ]
| { USER_RECYCLEBIN | RECYCLEBIN | DBA_RECYCLEBIN }

The PURGE TABLE command removes a specific table from the recycle bin. You can use either the original table name or the system-generated object name.
The PURGE TABLESPACE command removes all objects from a specific tablespace.
The PURGE RECYCLEBIN command removes all objects from the current user's recycle bin.

Example:

• Create a test table:
  

  CREATE TABLE TABLE_PURGE
  (
      ID NUMBER(1,0)
  );

• Drop the table:
  

  DROP TABLE table_purge;

• Display the contents of the recycle bin:
  

  SELECT object_name, original_name, type, ts_name, can_undrop, can_purge FROM user_recyclebin;

  Query result:
  

  OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME CAN_UNDROP CAN_PURGE
  ------------------------------ -------------------------------- ------------------------- ------------------------------ ---------- ---------
  BIN$kE4ti/oXT02ThCYJ+4263A==$0 TABLE_PURGE TABLE USERS YES YES

• Purge the table:
  

  PURGE TABLE table_purge;

After purging, the table is permanently removed from the database and no longer appears in the recycle bin views.