oracle purge作业,Oracle purge 用法介绍

1、用途：

清除oracle 回收站(recyclebin)中的表和索引并释放与其相关的空间，还可清空回收站，或者清除表空间中记录的已删除的部分表空间。

注意：purge后不能回滚和恢复。

2、语法：

3、示例说明：

1)首先查一下回收站：

SELECT * FROM RECYCLEBIN;

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

--版本1

CREATE TABLE recycle_tmp(version NUMBER(10));

INSERT INTO recycle_tmp VALUES(1);

COMMIT;

DROP TABLE recycle_tmp;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;回收站有1条记录：

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION DROPTIME

------------------------------ -------------------------------- --------- -------------------

BIN$Ncu0aCGFSXGwbg3KwS5nHA==$0 RECYCLE_TMP                      DROP      2014-05-28:22:00:06

--版本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;回收站有2条记录：

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

--版本3

CREATE TABLE recycle_tmp(version NUMBER(10));

INSERT INTO recycle_tmp VALUES(3);

COMMIT;

DROP TABLE recycle_tmp;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;回收站有3条记录：

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)清空版本1(默认删除最早版本)

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)清空版本2(根据回收站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)还原版本3(使用闪回flashback)

--还原版本3

FLASHBACK TABLE recycle_tmp TO BEFORE DROP;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;

未选定行(此时回收站已空)

SELECT * FROM recycle_tmp; VERSION

--------------------------

3

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

DROP TABLE recycle_tmp;

PURGE RECYCLEBIN;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN;回收站已清空。

未选定行(已清空)

-----------------------------

ByDylan.

原文：http://blog.csdn.net/indexman/article/details/27379597