Manage the Recycle Bin with DBA_RECYCLEBIN

calendar Sep 6, 2026 / · 9 min read · Oracle DBA DBA_RECYCLEBIN Recycle Bin PURGE Statement Flashback Table Dropped Objects USER_RECYCLEBIN Oracle Administration  ·
Share on: linkedin copy

Manage the Recycle Bin with DBA_RECYCLEBIN

Purpose

Where does a table actually go the instant DROP TABLE runs? Not straight to disk deallocation. Since Oracle Database 10g, a dropped table is renamed and moved into the recycle bin instead of being physically removed, and it stays there, fully intact, until something purges it or the tablespace runs out of room and reclaims the space automatically. DBA_RECYCLEBIN is the data dictionary view that exposes every recycle bin in the database at once — every user's dropped tables, indexes, and dependent objects, in one query, rather than checking one schema at a time.

DBA_RECYCLEBIN carries an OWNER column that its per-schema counterpart, USER_RECYCLEBIN, does not — the DBA view spans every schema in the database, while the user view (and its RECYCLEBIN synonym) is scoped to whichever schema is running the query. Each row also carries OPERATION, recording whether the object arrived in the recycle bin from a DROP or a TRUNCATE, though the view's own documentation is explicit that only dropped objects can currently be recovered — a truncated object's row exists for the record, not for retrieval. CAN_UNDROP and CAN_PURGE flag, per row, whether that specific object is still eligible for recovery or for permanent removal, and DROPTIME and DROPSCN record exactly when the drop happened, in wall-clock time and in the system change number a later Flashback Table statement can use to pin the recovery point precisely.

This post covers what each column in DBA_RECYCLEBIN reports, the PURGE statement family that permanently empties some or all of a recycle bin, the recovery path the recycle bin exists to support, and the RECYCLEBIN parameter that turns the whole mechanism on or off.

Code

 1-- Step 1: what is in the current user's own recycle bin
 2SELECT object_name, original_name, type, droptime, can_undrop, can_purge
 3FROM   user_recyclebin
 4ORDER  BY droptime DESC;
 5
 6-- Step 1b: the SQL*Plus shortcut for the same information
 7SHOW RECYCLEBIN;
 8
 9-- Step 1c: the RECYCLEBIN synonym returns the identical rows as USER_RECYCLEBIN
10SELECT * FROM recyclebin;
11
12-- Step 2: DBA-wide view -- every schema's dropped objects, one query
13SELECT owner, object_name, original_name, operation, type,
14       ts_name, droptime, can_undrop, can_purge, space
15FROM   dba_recyclebin
16ORDER  BY droptime DESC;
17
18-- Step 3: recover a dropped table instead of purging it
19FLASHBACK TABLE employees TO BEFORE DROP;
20
21-- Step 4a: purge one named table from your own recycle bin
22PURGE TABLE employees;
23
24-- Step 4b: purge by the system-generated recycle bin name (when duplicates exist)
25PURGE TABLE "RB$$33750$TABLE$0";
26
27-- Step 4c: purge a dropped index the same way
28PURGE INDEX emp_idx1;
29
30-- Step 5: empty your own entire recycle bin
31PURGE RECYCLEBIN;
32
33-- Step 6: empty the database-wide recycle bin (SYSDBA or PURGE DBA_RECYCLEBIN privilege)
34PURGE DBA_RECYCLEBIN;
35
36-- Step 7: reclaim recycle-bin space in one tablespace for one user over quota
37PURGE TABLESPACE users USER app_owner;
38
39-- Step 8: drop a table permanently, bypassing the recycle bin entirely
40DROP TABLE staging_temp PURGE;
41
42-- Step 9: control whether dropped objects go to the recycle bin at all
43ALTER SESSION SET recyclebin = OFF;
44ALTER SYSTEM SET recyclebin = ON;

Code Breakdown

Querying what is in the recycle bin

USER_RECYCLEBIN and its RECYCLEBIN synonym return the same rows — objects dropped by the currently connected schema — with no OWNER column, since scope is already implied. SHOW RECYCLEBIN is the SQL*Plus command-level equivalent for a quick look without writing a SELECT. DBA_RECYCLEBIN is the only one of the three that adds the OWNER column and returns dropped objects from every schema in the database in a single result set, which is why it needs elevated privileges (SYSDBA, or from Oracle Database 12c the dedicated PURGE DBA_RECYCLEBIN system privilege) to act on the whole thing rather than just view it.

Recovering with FLASHBACK TABLE

FLASHBACK TABLE employees TO BEFORE DROP is the recovery path the recycle bin exists to support. It reads the dropped object's entry — the same row DBA_RECYCLEBIN shows — and restores the table under its original name without touching a backup. CAN_UNDROP = 'YES' on the corresponding row is what confirms an object is still eligible for this before running the statement.

Purging by name

PURGE TABLE employees removes the named table and releases its space permanently. If more than one dropped object shares that original name, Oracle purges whichever has been in the recycle bin the longest; the system-generated name (the RB$$... form visible in OBJECT_NAME) is unique, so passing that instead removes one specific object with no ambiguity. Purging a table also purges everything attached to it — partitions, LOBs, LOB partitions, indexes, and other dependent objects — in the same statement. PURGE INDEX follows the identical pattern for a dropped index.

Emptying a whole recycle bin

PURGE RECYCLEBIN clears every object out of the current user's own recycle bin in one statement. PURGE DBA_RECYCLEBIN is the system-wide equivalent — every user's recycle bin at once — and is restricted to SYSDBA or the PURGE DBA_RECYCLEBIN system privilege specifically because of that scope.

Reclaiming space by tablespace or by user

PURGE TABLESPACE <name> USER <user> targets a specific user's dropped objects within one tablespace or tablespace set, which is the narrower tool when the actual problem is one schema running low on quota rather than the recycle bin in general.

Bypassing the recycle bin entirely

DROP TABLE staging_temp PURGE skips the recycle bin step altogether — the table is dropped and its space released in the same statement, with no intermediate recoverable state. This is the opposite intent of every other statement above: instant, permanent removal instead of a recoverable interim.

Turning the mechanism on or off

ALTER SESSION SET recyclebin = OFF disables the recycle bin for the current session only; objects that session drops afterward are removed immediately rather than parked. ALTER SYSTEM SET recyclebin = ON re-enables it database-wide. The recycle bin has been enabled by default since its introduction in Oracle Database 10g, and disabling it at the system level is not the recommended default state.

Key Points

  • PURGE cannot be rolled back and the object cannot be recovered afterward — it is the one-way half of the recycle bin workflow, opposite FLASHBACK TABLE.
  • Only dropped objects are recoverable, not truncated ones. OPERATION = 'TRUNCATE' rows exist in DBA_RECYCLEBIN for the record, but the current view documentation states plainly that truncated objects cannot be recovered from the recycle bin.
  • DBA_RECYCLEBIN adds OWNER; USER_RECYCLEBIN does not. Everything else about the column set is shared between the two views.
  • CAN_UNDROP and CAN_PURGE are per-row flags, not database-wide settings — one dropped object in the recycle bin can be eligible for one operation and not the other.
  • Purging a table cascades. Partitions, LOB segments, LOB partitions, indexes, and other dependent objects on that table are purged in the same statement, not left behind as orphaned rows.
  • DROP TABLE ... PURGE and PURGE TABLE <name> solve different timing problems — the first skips the recycle bin at drop time, the second empties an object already sitting there.

Insights and Best Practices

Check CAN_PURGE and CAN_UNDROP before scripting cleanup

A cleanup job written against DBA_RECYCLEBIN that purges every row older than some age threshold should filter on CAN_PURGE = 'YES' first. Not every row is guaranteed eligible, and a script that assumes otherwise can fail partway through a batch purge instead of skipping the ineligible rows cleanly.

Reach for FLASHBACK TABLE before a restore

For an accidental DROP TABLE caught while the object is still sitting in the recycle bin, FLASHBACK TABLE ... TO BEFORE DROP recovers it in one statement with no backup restore, no downtime, and no coordination with a separate backup process. Checking DBA_RECYCLEBIN first — filtering by ORIGINAL_NAME and DROPTIME — confirms the object is actually still there and CAN_UNDROP = 'YES' before assuming the recovery will succeed.

Purge ahead of a downgrade or backward migration

The PURGE DBA_RECYCLEBIN clause is documented as useful specifically before a backward migration — clearing every user's recycle bin ahead of that kind of operation avoids carrying dropped-object metadata into a process that does not expect it.

Leave RECYCLEBIN enabled

Disabling the recycle bin database-wide with ALTER SYSTEM SET recyclebin = OFF removes the safety net FLASHBACK TABLE depends on for every dropped object going forward — an accidental drop after that point goes straight to physical removal. Toggling it off for a single session with ALTER SESSION SET recyclebin = OFF is the narrower, safer version when a specific script genuinely needs immediate space reclamation rather than recoverable drops.

Use PURGE TABLESPACE ... USER for quota problems, not PURGE DBA_RECYCLEBIN

When the actual issue is one schema hitting its tablespace quota because of accumulated dropped objects, PURGE TABLESPACE <name> USER <user> reclaims exactly that space without touching every other user's recycle bin the way PURGE DBA_RECYCLEBIN would.

When to Use This

  • Confirming whether an accidentally dropped table is still recoverable before deciding whether a backup restore is even necessary.
  • Auditing recycle bin space consumption across every schema from one query, ahead of a storage or quota review.
  • Reclaiming space for one specific schema that has hit its tablespace quota due to accumulated dropped objects.
  • Clearing dropped-object metadata database-wide ahead of a backward migration or major version change.
  • Deciding, for a staging or scratch table that will never need recovery, whether to drop it with PURGE up front instead of leaving it recoverable.

Troubleshooting Common Issues

PURGE TABLE <name> returns an error about insufficient privileges. Purging a table requires the table to be in your own schema, or the DROP ANY TABLE system privilege, or SYSDBA. The equivalent applies to indexes with DROP ANY INDEX. PURGE DBA_RECYCLEBIN specifically requires SYSDBA or the PURGE DBA_RECYCLEBIN system privilege — neither DROP ANY TABLE nor ordinary DBA role membership substitutes for it.

Space doesn't appear reclaimed right after DROP TABLE. That is expected — the table moved into the recycle bin rather than being physically removed. Check DBA_RECYCLEBIN for the row and issue PURGE TABLE <name> (or PURGE RECYCLEBIN for everything) to actually release the space.

More than one row in the recycle bin shares the same ORIGINAL_NAME. A PURGE TABLE <original_name> or FLASHBACK TABLE <original_name> in that case acts on whichever object has been there longest. Query DBA_RECYCLEBIN for the exact system-generated OBJECT_NAME and use that instead when a specific one of several duplicates needs to be targeted.

A dropped table won't recover. Check OPERATION on its row first — a TRUNCATE row cannot be recovered from the recycle bin regardless of CAN_UNDROP, and only a DROP row is a valid FLASHBACK TABLE target.

References

Posts in this series