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; 回收站已清空。 未选定行(已清空)