oracle purge作业,Oracle purge用法介绍

1、用途：

清除oracle 回收站(recyclebin)中的表和索引并释放与其相关的空间，还可清空回收站，或者清除表空间中记录的已删除的部分表空间。 注意：purge后不能回滚和恢复。

2、语法：

3、示例说明：

1)首先查一下回收站： [sql] view plain copy

SELECT * FROM RECYCLEBIN;

2)创建并删除同一表三次： [sql] view plain copy

--版本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

[sql] view plain copy

--版本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

[sql] view plain copy

--版本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(默认删除最早版本) [sql] view plain copy

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清除表) [sql] view plain copy

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) [sql] view plain copy

--还原版本3

FLASHBACK TABLE recycle_tmp TO BEFORE DROP;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN; 未选定行(此时回收站已空)

[sql] view plain copy

SELECT * FROM recycle_tmp; VERSION

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

3

6)删除版本3，后清空回收站 [sql] view plain copy

DROP TABLE recycle_tmp;

PURGE RECYCLEBIN;

SELECT object_name, original_name, operation, droptime FROM RECYCLEBIN; 回收站已清空。 未选定行(已清空)