Oraclepurge用法介紹

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;

回收站已清空。 未選定行（已清空）







----------------------------- By Dylan.