SQL PURGE

What is the SQL PURGE statement?

Permanently deletes objects from the Oracle recycle bin (or other purge-eligible storage) so they cannot be flashed back or recovered.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PURGE: Oracle Database 10g and later – full support. MySQL – has a different statement (PURGE BINARY LOGS) that is unrelated. PostgreSQL, SQL Server, SQLite, Snowflake – not supported.

SQL PURGE Full Explanation

The SQL PURGE statement is an Oracle-specific administrative command that irrevocably removes dropped database objects or entire recycle bins. When you issue a normal DROP, Oracle moves the object to the recycle bin so it can be restored with FLASHBACK. PURGE skips or empties that safety net and releases the space immediately. You can purge a single table, index, materialized view log, or an entire tablespace’s recycle bin. DBAs can also purge every user’s recycle bin with PURGE DBA_RECYCLEBIN. Once PURGE executes, the object’s metadata and data blocks are marked free and cannot be recovered with FLASHBACK TABLE or FLASHBACK DROP. Because the operation is permanent, Oracle requires the same privileges as DROP plus specific object ownership or the SYSTEM privilege DROP ANY. Use PURGE only when you are certain the object is no longer needed or when reclaiming space is urgent.

SQL PURGE Syntax

-- Purge the current user’s entire recycle bin
PURGE RECYCLEBIN;

-- Purge all recycle-bin objects in the database (DBA only)
PURGE DBA_RECYCLEBIN;

-- Purge a specific table
PURGE TABLE schema_name.table_name;

-- Purge a specific index
PURGE INDEX schema_name.index_name;

-- Purge all dropped objects from a tablespace
PURGE TABLESPACE tablespace_name;

-- Purge dropped objects from a tablespace for one user
PURGE TABLESPACE tablespace_name USER user_name;

SQL PURGE Parameters

object_type (STRING) - Required. TABLE, INDEX, TABLESPACE, RECYCLEBIN, or DBA_RECYCLEBIN indicating what to purge.
object_name (IDENTIFIER) - Name of the table, index, or tablespace to purge (not used with RECYCLEBIN or DBA_RECYCLEBIN).
USER (IDENTIFIER) - Optional when purging a tablespace; limits the operation to objects owned by a specific user.

Example Queries Using SQL PURGE

-- 1. Drop then irreversibly remove a staging table
DROP TABLE hr.stage_sales;
PURGE TABLE hr.stage_sales;

-- 2. Free space taken by dropped objects in the USERS tablespace
PURGE TABLESPACE users;

-- 3. Empty your personal recycle bin
PURGE RECYCLEBIN;

-- 4. DBA clears every recycle bin in the database
PURGE DBA_RECYCLEBIN;

Expected Output Using SQL PURGE

The specified objects are erased from the recycle bin
Space is released to the tablespace
FLASHBACK operations for those objects become impossible

Use Cases with SQL PURGE

Reclaim tablespace space quickly after bulk drops.
Remove sensitive data so it cannot be restored.
Clean up environments before exporting or cloning.
Maintain compliance rules that forbid recoverable drops.

Common Mistakes with SQL PURGE

Assuming PURGE can be undone; it cannot.
Forgetting to qualify the schema, which can purge an object with the same name in the current schema.
Attempting to purge an object that was never dropped; drop it first.
Lack of privileges; PURGE requires ownership or DROP ANY privilege.

First Introduced In

Oracle Database 10g Release 1 (2003)

Frequently Asked Questions

What happens if I forget to specify a schema?

Oracle purges the object from the current schema. Always fully qualify names when multiple schemas contain similarly named objects.

Does PURGE free disk space immediately?

Yes. Oracle marks the data blocks as free so subsequent inserts or extensions can reuse them without waiting for space reclamation jobs.

Is PURGE faster than DROP?

DROP is usually faster because it only renames the object into the recycle bin. PURGE must update additional data dictionary tables to remove all metadata. Use DROP for quick removal and PURGE afterward if recovery is not needed.

Can I purge a table that is still referenced by a foreign key?

No. Drop the foreign key or purge the referencing objects first. Otherwise Oracle raises ORA-00604/ORA-30501 dependency errors.