Recycle Bin Fundamentals

Oracle introduced the Tablespace Recycle Bin in version 10g, providing a logical container for dropped database objects. Unlike a dedicated physical storage area, the Recycle Bin shares space with its host tablespace. When objects are dropped, they remain recoverable until space pressure triggers automatic purging using a FIFO algorithm or manual intervention occurs. This mechanism stores metadata in dictionary tables while retaining actual data blocks.

Enabling and Disabling

Verify Recycle Bin status using:

SHOW PARAMETER RECYCLEBIN;
-- Alternative query:
SELECT name, value FROM v$parameter WHERE name = 'recyclebin';

Modify configuration at system or session level:

-- Disable
ALTER SYSTEM SET recyclebin = OFF;
ALTER SESSION SET recyclebin = OFF;

-- Enable
ALTER SYSTEM SET recyclebin = ON;
ALTER SESSION SET recyclebin = ON;

Object Restoration

After dropping a table:

CREATE TABLE user_data (id NUMBER);
INSERT INTO user_data VALUES (1);
COMMIT;
DROP TABLE user_data;

View Recycle Bin objects:

SHOW RECYCLEBIN;
-- Alternative queries
SELECT object_name, original_name, droptime FROM recyclebin;
SELECT * FROM user_recyclebin; -- Current user
SELECT * FROM dba_recyclebin;  -- Requires privileges

Restore objects using Flashback Drop:

FLASHBACK TABLE user_data TO BEFORE DROP;

Handle name conflicts during restoration:

-- Restore with new name
FLASHBACK TABLE user_data TO BEFORE DROP RENAME TO restored_data;

-- Restore by system-generated name
FLASHBACK TABLE "BIN$J38s87HjN9gvMKLpQaRqBw==$0" TO BEFORE DROP;

Space Reclamation

Permanent deletion methods:

-- Purge specific object
PURGE TABLE user_data;
PURGE TABLE "BIN$J38s87HjN9gvMKLpQaRqBw==$0";

-- Purge entire tablespace
PURGE TABLESPACE users;

-- User-specific purge
PURGE TABLESPACE users USER scott;

-- Clear user's Recycle Bin
PURGE RECYCLEBIN;

-- Database-wide purge (DBA required)
PURGE DBA_RECYCLEBIN;

Operational Considerations

Key limitations include:

• System tablespace objects bypass the Recycle Bin
• Foreign key constraints require manual reconstruction
• Dependent materialized views aren't restored automatical
• Space reuse may prevent object recovery
• Only SELECT operations are permitted on Recycle Bin objects

Index restoration requires manual renaming:

ALTER INDEX bin$idx123 RENAME TO user_data_idx;