首页 > 代码库 > 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
------------------------------ -------------------------------- --------- -------------------
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.
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。