PURGE

Purpose

Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

Caution: You cannot roll back a PURGE statement, nor can you recover an object after it is purged.

Examples

Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, Oracle Database removes the version that has been there the longest:

PURGE TABLE test;

To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to the following statement. (The system-generated name will differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

The recyclebin is enabled, by default, from Oracle 10g.
But you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.
SQL> ALTER SYSTEM/SESSION SET RECYCLEBIN=ON/OFF SCOPE=BOTH;
SQL> SHOW PARAMETER RECYCLEBIN

When the recycle bin is enabled, dropped tables and their dependent objects are placed in the recycle bin.