Oracle Purge Usage Introduction

1. Use: clears the tables and indexes in the Oracle Recycle Bin (recyclebin) and frees the space associated with them, empties the Recycle Bin, or clears some of the deleted table spaces that are recorded in the tablespace. Note: You cannot rollback and recover after purge.
2. Syntax:     
3. Example Description:
1) First look at the Recycle Bin:   

SELECT * from RecycleBin;

2) Create and delete the same table three times:   

--Version 1CREATE TABLE recycle_tmp (version number); INSERT into Recycle_tmp VALUES (1); Commit;drop TABLE recycle_tmp; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

The Recycle Bin has 1 records:
object_name original_name Operation Droptime
------------------------------ -------------------------------- --------- -------------------
Bin$ncu0acgfsxgwbg3kws5nha==$0 recycle_tmp DROP 2014-05-28:22:00:06

--Version 2CREATE TABLE recycle_tmp (version number); INSERT into Recycle_tmp VALUES (2); Commit;drop TABLE recycle_tmp; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

The Recycle Bin has 2 records:
object_name original_name Operation Droptime
------------------------------ -------------------------------- --------- -------------------
Bin$34btcie8ryspmhqilcgnvg==$0 recycle_tmp DROP 2014-05-28:22:03:49
Bin$ncu0acgfsxgwbg3kws5nha==$0 recycle_tmp DROP 2014-05-28:22:00:06

--Version 3CREATE TABLE recycle_tmp (version number); INSERT into Recycle_tmp VALUES (3); Commit;drop TABLE recycle_tmp; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

The Recycle Bin has 3 records:
object_name original_name Operation Droptime
------------------------------ -------------------------------- --------- -------------------
Bin$34btcie8ryspmhqilcgnvg==$0 recycle_tmp DROP 2014-05-28:22:03:49
Bin$pntkhku4ty+qyyiqhtsdgg==$0 recycle_tmp DROP 2014-05-28:22:04:02
bin$ncu0acgfsxgwbg3kws5nha==$0 recycle_tmp DROP 2014-05-28:22:00:06

3)Clear version 1 (the oldest version is deleted by default)

PURGE TABLE recycle_tmp; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

object_name original_name Operation Droptime
------------------------------ -------------------------------- --------- -------------------
bin$34btcie8ryspmhqilcgnvg==$0 recycle_tmp DROP 2014-05-28:22:03:49
bin$pntkhku4ty+qyyiqhtsdgg==$0 recycle_tmp DROP 2014-05-28:22:04:02

4) Clear version 2 (clears the table according to the Recycle Bin object_name)

PURGE TABLE  "bin$34btcie8ryspmhqilcgnvg==$0"; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

object_name original_name Operation Droptime
------------------------------ -------------------------------- --------- -------------------
bin$pntkhku4ty+qyyiqhtsdgg==$0 recycle_tmp DROP 2014-05-28:22:04:02

5) Restore version 3 (using flashback flashback)

--Restore version 3FLASHBACK TABLE recycle_tmp to before DROP; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

no row selected (Recycle Bin is empty at this time)

SELECT * from Recycle_tmp;

VERSION
--------------------------
3

6) Delete version 3, then empty recycle Bin

DROP TABLE recycle_tmp; PURGE RecycleBin; SELECT object_name, Original_name, operation, Droptime from RecycleBin;

The Recycle Bin has been emptied.unselected rows (emptied)







-----------------------------by Dylan.