PURGE 的用法说明

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.
使用PURGE语句从回收站删除一个表或索引，并释放与该对象关联的所有空间，或者删除整个回收站，或者删除回收站中丢弃的表空间的一部分。

Caution:
You cannot roll back a PURGE statement, nor can you recover an object after it is purged.
注意:purge后不能回滚和恢复

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:
要查看回收站的内容，请查询user_UNK clebin数据字典评审。可以使用回收的同义词。
以下两个语句返回相同的行:
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

Prerequisites(先决条件)
The database object must reside in your own schema or you must have the DROP ANY... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege.
数据库对象必须驻留在自己的模式中，否则您必须DROP ANY…要清除的对象类型的系统特权，或者必须具有SYSDBA权限

Syntax(语法)

TABLE or INDEX
Specify the name of the table or index in the recycle bin that you want to purge. You can specify either the original user-specified name or the system-generated name Oracle Database assigned to the object when it was dropped.
指定要清除的回收站中的表或索引的名称。可以指定原始用户指定的名称或系统生成的名称Oracle数据库，该数据库被丢弃时指定给该对象。

• If you specify the user-specified name, and if the recycle bin contains more than one object of that name, then the database purges the object that has been in the recycle bin the longest.
如果指定用户指定的名称，并且如果回收站包含不止一个该名称的对象，那么数据库就会清除在回收站中最长的对象。

• System-generated recycle bin object names are unique. Therefore, if you specify the system-generated name, then the database purges that specified object.
系统生成的回收站对象名是唯一的。因此，如果指定系统生成的名称，那么数据库就会清除指定的对象。

When the database purges a table, all table partitions, LOBs and LOB partitions, indexes, and other dependent objects of that table are also purged.
当数据库清理一个表时，所有表分区、LOB和LOB分区、索引以及该表的其他相关对象也被清除。

RECYCLEBIN
Use this clause to purge the current user's recycle bin. Oracle Database will remove all objects from the user's recycle bin and release all space associated with objects in the recycle bin.
使用此子句清除当前用户的回收站。Oracle数据库将删除用户回收站中的所有对象，并释放与回收站中的对象相关的所有空间。

DBA_RECYCLEBIN
This clause is valid only if you have SYSDBA system privilege. It lets you remove all objects from the system-wide recycle bin, and is equivalent to purging the recycle bin of every user. This operation is useful, for example, before backward migration.
此条款仅当拥有SYSDBA系统特权时才有效。它允许从系统范围内的回收站中删除所有对象，并且相当于清除每个用户的回收站。例如，该操作在向后迁移之前是有用的。

TABLESPACE tablespace
Use this clause to purge all the objects residing in the specified tablespace from the recycle bin.
使用此子句将所有驻留在指定表空间中的对象从回收站中清除。

USER user 
Use this clause to reclaim space in a tablespace for a specified user. This operation is useful when a particular user is running low on disk quota for the specified tablespace.
用户使用此子句在指定用户的表空间中回收空间。当特定的用户在指定的表空间上运行低磁盘配额时，此操作非常有用。

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:
从回收站删除一个文件:例如下面的语句从回收站删除了表测试。如果多个版本的测试驻留在回收站中，Oracle数据库删除了在那里最长的版本:

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.)
要确定从回收站中删除的表的系统生成的名称，请在回收站上发布一个SELECT语句。使用该对象名称，可以通过发出类似以下语句的语句删除表。(系统生成的名称与示例中显示的名称不同)。
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;

示例:

1) 首先查一下回收站：
Select * From RECYCLEBIN;

2）创建并删除同一表三次：

CREATE TABLE recycle_tmp(version NUMBER(10));  
INSERT INTO recycle_tmp VALUES(1);  
COMMIT;  
SELECT * FROM RECYCLE_TMP;
DROP TABLE recycle_tmp;  
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN; 

 此时回收站只有一条记录:

再重复2)的操作,此时回收站有两条记录:

CREATE TABLE recycle_tmp(version NUMBER(10));  
INSERT INTO recycle_tmp VALUES(2);  
COMMIT;  
DROP TABLE recycle_tmp;  
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN; 

 
3）清空版本1（默认删除最早版本）

PURGE TABLE recycle_tmp;   
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;

 

4）清空版本2（根据回收站OBJECT_NAME清除表）

PURGE TABLE  "BIN$jw65nLiqSTO82jqDOGghDg==$0";  
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;

5）还原版本（使用闪回flashback）

FLASHBACK TABLE recycle_tmp TO BEFORE DROP;  
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;

此时回收站已空

6）删除版本3，后清空回收站

DROP TABLE recycle_tmp;  
PURGE RECYCLEBIN;   
SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN; 

回收站以清空