Monday, March 21, 2011

DROP TABLE - PURGE

Oracle Database 10g gives you the extra feature to drop your table and put them in the Recycle bin, This feature was introduced in 10g and it's not in earlier versions. Once you drop the table you can use Flashback query to retrieve that table from the Recycle bin.
If you want to remove the table permanently from the database also from Recycle bin then you have to use PURGE (just like shift+delete key in Windows).
And you cannot roll back Drop Table and Purge Clause.

Syntax given below.

Drop Table Table_name Purge;
It'll permanently remove and clears your storage space.

Drop Table Table_name
It will drop the table and send that table into the Recycle Bin.

Flashback Table Table_name to Before drop;
It will retrieve the Table from the Recycle Bin.

And also you can use this query to retrieve from Recycle Bin.

Select original_name, operation, droptime, from recyclebin;
Where Original_name is the table_name
Operation is drop that you did perform
Droptime, At what time you dropped the table.

To Clear the RecycleBin,
PURGE RECYCLEBIN;

No comments:

Post a Comment