Oracle Purge Recyclebin

Oracle Purge Recyclebin | In this tutorial we will see how to purge recyclebin in the Oracle database. Let’s go through the complete flow of the purge in Oracle. First of all, let us discuss what is purge?

When we drop the table in the Oracle database then, the data gets stored in the recycle bin. Once even after dropping the table we can get back the table using the FLASHBACK command. But once we purge the table then the table data, dependent objects, and the table gets deleted permanently and we cannot recollect it back.

PURGE:- It will purge all the dependent objects and the table so that they don’t appear in the recycle bin. In the Oracle database we can use the following query to purge recyclebin:-

PURGE user_recyclebin;

Or,

PURGE recyclebin;

Let’s get into the flow of the purge process in Oracle. We will demonstrate Oracle purge recyclebin 12c.

Step-1:- Let’s first create a table and insert some data in it.

CREATE TABLE test (
   id NUMBER,
   name  VARCHAR2(15)
);
INSERT INTO test VALUES(10, 'BLAKE');
INSERT INTO test VALUES(20, 'JONES');
SELECT * FROM test;

The output of select query:-

        ID NAME
---------- ---------------
        10 BLAKE
        20 JONES

Step-2:- Before dropping the table we’ll check any data that can be found in recycle bin.

SELECT * FROM user_recyclebin;

Output:-

No data found

If the user_recyclebin contains some data then you can delete permanently using the below query, so that we can demonstrate this example very easily.

PURGE user_recyclebin;

Step-3:- Drop the table. See more:- DROP command in Oracle

DROP TABLE test;

Output:-

Table dropped.

Step-4:- Check the Recycle Bin.

By checking the recycle bin we can confirm whether dropped table comes to recycle bin or not.

SELECT object_name, operation FROM user_recyclebin;

Output:-

OBJECT_NAME                    OPERATION
------------------------------ ---------
BIN$rx/DNUs5RPmTPo8+CkjfiQ==$0 DROP

In the recycle bin, the object name is listed which is the dropped table TEST. You can restore the table using the FLASHBACK command. Execute the below queries for demonstration.

FLASHBACK TABLE test TO BEFORE DROP;
SELECT * FROM user_recyclebin;
SELECT * FROM test;
DROP TABLE test;
SELECT * FROM user_recyclebin;

The first query will restore the dropped table, the second query will show the user_recyclebin, the third query will show the test table, the fourth query will again drop the table, and the fifth query will show user_recyclebin.

Step-5:- Purge the recyclebin

Let’s purge the recycle bin which contains the dropped tables data objects.

PURGE user_recyclebin;

We can verify if the data objects are removed permanently or not by displaying the user_recyclebin.

SELECT * FROM user_recyclebin;

Output:-

No data found 

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search