闪回删除
语法:
FLASHBACK TABLE [schema.]<table_name> {BEFORE DROP [RENAME TO
table_new_name]}
回收站中表名的含义:(格式: BIN$globalUID$version)
BIN: 表示 RECYCLEBIN
globalUID: 是一个全局唯一的, 24 个字节,该标识与原对象名没有任何关系
version: 指数据库分配的版本号。
闪回删除利用的是recyclebin回收站,闪回删除特指是drop操作,drop后的表实际上是存放在recyclebin里的,还是可以对删除后的表进行查询操作(基于回收站);但是注意:当表空间用满或设置为自动扩展了,但对于要扩张表空间首先会侵占回收站的空间,这就导致回收站内的数据彻底丢失!!!!
回收站采用后进先出原则;假设当t表被drop后,有建了一新的同名t表,则flashback table就必须要重命名rename回收站的老t表了;
当然也可以指定回收站内的表明(特指在回收站内新生成的名字)进行flashback table操作;
一:测试闪回删除
使用闪回删除前提启动闪回回收站:
SYS@ORA11GR2>show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
(默认状态下,回收站是启用的/on的状态)
1) 准备环境(创建测试表 t,索引 idx_t,触发器 trg_t)
——在xx用户下创建测试表
XX@ORA11GR2>create table t(x number(2),d date);
Table created.
XX@ORA11GR2>create unique index idx_t on t(x);
Index created.
XX@ORA11GR2>create or replace trigger trg_t
2 before insert on t
3 for each row
4 begin
5 if :new.d is null then
6 :new.d:=sysdate;
7 end if;
8 end;
9 /
Trigger created.
2)删除测试表t,验证已进入回收站
XX@ORA11GR2>drop table t;
Table dropped.
XX@ORA11GR2>select tname,tabtype from tab;
TNAME TABTYPE
------------------------------ -------
BIN$Pa549j+PX+LgUwEAAH+ELA==$0 TABLE
SYS_FBA_DDL_COLMAP_89859 TABLE
SYS_FBA_HIST_89859 TABLE
SYS_FBA_TCRV_89859 TABLE
T1 TABLE
T2 TABLE
6 rows selected.
——查看回收站内的表:
XX@ORA11GR2>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$Pa549j+PX+LgUwEAAH+ELA==$0 TABLE 2016-09-30:07:02:31
——只看到了表,没有看到表关联的索引、触发器,再利用user_recyclebin查询:
XX@ORA11GR2>desc user_recyclebin
XX@ORA11GR2>select ORIGINAL_NAME,OBJECT_NAME,TYPE,DROPTIME from user_recyclebin;
ORIGINAL_N OBJECT_NAME TYPE DROPTIME
---------- ------------------------------ -------- -------------------
IDX_T BIN$Pa549j+NX+LgUwEAAH+ELA==$0 INDEX 2016-09-30:07:02:31
TRG_T BIN$Pa549j+OX+LgUwEAAH+ELA==$0 TRIGGER 2016-09-30:07:02:31
T BIN$Pa549j+PX+LgUwEAAH+ELA==$0 TABLE 2016-09-30:07:02:31
(注 有一定权限的话,也可以查看 dba_recyclebin 这个视图)
3) 对删除的表进行闪回,闪回成功后,回收站已经没有测试表的记录
XX@ORA11GR2>flashback table t to before drop;
Flashback complete.
验证:
XX@ORA11GR2>select ORIGINAL_NAME,OBJECT_NAME,TYPE,DROPTIME from user_recyclebin;
no rows selected
4) 查看闪回的表相关对象的状态
通过以下查询,发现以下两个问题: 1:触发器的状态为失效的; 2:对象名称,除表名以外,其他的都已不是原来的名称。
XX@ORA11GR2>col OBJECT_NAME for a30
XX@ORA11GR2>select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
BIN$Pa549j+NX+LgUwEAAH+ELA==$0 INDEX VALID
BIN$Pa549j+OX+LgUwEAAH+ELA==$0 TRIGGER INVALID
T TABLE VALID
SYS_FBA_TCRV_IDX_89859 INDEX VALID
SYS_FBA_TCRV_89859 TABLE VALID
SYS_FBA_HIST_89859 TABLE VALID
SYS_FBA_HIST_89859 TABLE PARTITION VALID
SYS_FBA_DDL_COLMAP_89859 TABLE VALID
T2 TABLE VALID
T1 TABLE VALID
10 rows selected.
5) 重新编译触发器,并重命名触发器
——先重新编译:
XX@ORA11GR2>alter trigger "BIN$Pa549j+OX+LgUwEAAH+ELA==$0" compile;
Trigger altered.
——再重命名:
XX@ORA11GR2>alter trigger "BIN$Pa549j+OX+LgUwEAAH+ELA==$0" rename to trg_t;
Trigger altered.
验证:
XX@ORA11GR2>select object_name,object_type,status from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------- -------
TRG_T TRIGGER VALID
BIN$Pa549j+NX+LgUwEAAH+ELA==$0 INDEX VALID
T TABLE VALID
SYS_FBA_TCRV_IDX_89859 INDEX VALID
SYS_FBA_TCRV_89859 TABLE VALID
SYS_FBA_HIST_89859 TABLE VALID
SYS_FBA_HIST_89859 TABLE PARTITION VALID
SYS_FBA_DDL_COLMAP_89859 TABLE VALID
T2 TABLE VALID
T1 TABLE VALID
10 rows selected.
6)再次删t除表:(这是实验删除表进回收站,而后用户xx又建了一个同名的t表的闪回表的操作)
XX@ORA11GR2>drop table t;
Table dropped.
XX@ORA11GR2>select original_name,object_name,type from user_recyclebin;
ORIGINAL_NAME OBJECT_NAME TYPE
-------------------------------- ------------------------------ --------
BIN$Pa549j+NX+LgUwEAAH+ELA==$0 BIN$Pa549j+QX+LgUwEAAH+ELA==$1 INDEX
TRG_T BIN$Pa549j+RX+LgUwEAAH+ELA==$0 TRIGGER
T BIN$Pa549j+SX+LgUwEAAH+ELA==$0 TABLE
——创建一张新的 t 表(包含两个字段,目的是将 t 的对象名称占用上)
XX@ORA11GR2>create table t(x int,y int);
Table created.
XX@ORA11GR2>
——闪回 t 表(包含一个字段的那个表,即回收站中的对象)由于对象 t 已经存在,直接闪回会报错,闪回删除提供 rename 功能,即闪回后重命名
XX@ORA11GR2>flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
--解决(重命名):
XX@ORA11GR2>flashback table t to before drop rename to t_old;
Flashback complete.
XX@ORA11GR2>select * from tab where tname like'T%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_OLD TABLE
T2 TABLE
T1 TABLE
T TABLE
验证回收站:(无显示,即为无表在回收站内)
XX@ORA11GR2>show recyclebin;
XX@ORA11GR2>
(此时需要注意重新闪回回来的表t_old的触发器状态还是invalid(查user_objects),需要重新编译后再重命名;)
二:实验回收站之 后进先出原则:
如回收站中存在两个(以上) 相同的源表名,则闪回时总是闪回最近的版本,如果闪回特定
的表,需要指定该表在回收站中的名称。 测试如下:
1.创建表 hehe,创建三次,删除三次,第一次一个字段,第二次两个字段,第三次三个字段
XX@ORA11GR2>conn scott/tiger
Connected.
SCOTT@ORA11GR2>create table hehe(x1 int);
Table created.
SCOTT@ORA11GR2>drop table hehe;
Table dropped.
SCOTT@ORA11GR2>create table hehe(x1 int,x2 int);
Table created.
SCOTT@ORA11GR2>drop table hehe;
Table dropped.
SCOTT@ORA11GR2>create table hehe(x1 int,x2 int,x3 int);
Table created.
SCOTT@ORA11GR2>drop table hehe;
Table dropped.
2.查看用户scott的回收站:
SCOTT@ORA11GR2>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
HEHE BIN$Pa8DJnKCasvgUwEAAH+7Hg==$0 TABLE 2016-09-30:07:43:02
HEHE BIN$Pa8DJnKBasvgUwEAAH+7Hg==$0 TABLE 2016-09-30:07:41:40
HEHE BIN$Pa8DJnKAasvgUwEAAH+7Hg==$0 TABLE 2016-09-30:07:41:10
或者:
SCOTT@ORA11GR2>SELECT original_name, object_name FROM user_recyclebin where original_name='HEHE';
3.闪回 hehe 表,我们查看返回后的 hehe 表,发现为最后删除的、包含三个字段的 hehe 表
SCOTT@ORA11GR2>flashback table hehe to before drop;
Flashback complete.
SCOTT@ORA11GR2>desc hehe
Name Null? Type
----------------------------------------- -------- ----------------------------
X1 NUMBER(38)
X2 NUMBER(38)
X3 NUMBER(38)
(最后进回收站的,闪回时最先出,即后进先出原则)
4.现在回收站中还有两个‘ hehe’表,要求闪回包含一个字段的 hehe 表,并且重命名为 haha
SCOTT@ORA11GR2>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
HEHE BIN$Pa8DJnKBasvgUwEAAH+7Hg==$0 TABLE 2016-09-30:07:41:40
HEHE BIN$Pa8DJnKAasvgUwEAAH+7Hg==$0 TABLE 2016-09-30:07:41:10
SCOTT@ORA11GR2>desc "BIN$Pa8DJnKAasvgUwEAAH+7Hg==$0"
Name Null? Type
----------------------------------------- -------- ----------------------------
X1 NUMBER(38)
(要闪回的就是它,对于回收站内的表,一样可以查询select * from "BIN$Pa8DJnKAasvgUwEAAH+7Hg==$0";但是不能对回收站的表进行DDL/DML操作)
——进行闪回操作
SCOTT@ORA11GR2>flashback table "BIN$Pa8DJnKAasvgUwEAAH+7Hg==$0" to before drop rename to haha;
Flashback complete.
验证(回收成功):
SCOTT@ORA11GR2>desc haha;
Name Null? Type
----------------------------------------- -------- ----------------------------
X1 NUMBER(38)
三: 清空回收站
1) 将整个表空间的回收站内容全部清空:
SCOTT@ORA11GR2>purge tablespace users;
Tablespace purged.
2) 清空某个表空间上某个用户回收站中的对象:
SCOTT@ORA11GR2>purge tablespace users user scott;
Tablespace purged.
3) 清空当前用户下回收站:
SCOTT@ORA11GR2>purge recyclebin;
Recyclebin purged.
4) 当以DBA身份登录时,可以清空所有表空间上回收站 :
SYS@ORA11GR2>purge dba_recyclebin;
DBA Recyclebin purged.
SYS@ORA11GR2>purge recyclebin;
Recyclebin purged.
回收站注意事项:
ü 闪回删除不适用于:
- 驻留在 SYSTEM 表空间中的表
- 使用细粒度级审计或虚拟专用数据库的表
- 驻留在字典管理表空间中的表
- 已清除的表,无论是手动清除的,还是在空间压力下自动清除的
ü 以下相关对象不受保护:
- 位图联接索引
- 实体化视图日志
- 引用完整性约束条件
- 在删除表前删除的索引
1) 使用 system 表空间的表,删除不进入回收站
SYS@ORA11GR2>conn xx/oracle
Connected.
XX@ORA11GR2>
XX@ORA11GR2>create table ha tablespace system as select 1 as x from dual;
Table created.
XX@ORA11GR2>select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T1 TS_USERS
T2 TS_USERS
SYS_FBA_DDL_COLMAP_89859 TS_USERS
SYS_FBA_TCRV_89859 TS_USERS
T_OLD TS_USERS
T TS_USERS
HA SYSTEM
SYS_FBA_HIST_89859
8 rows selected.
XX@ORA11GR2>drop table ha;
Table dropped.
XX@ORA11GR2>show recyclebin;
XX@ORA11GR2>
或者:
XX@ORA11GR2>select original_name,object_name,type,droptime from user_recyclebin;
no rows selected
不进回收站也就意味着无法闪回删除的使用system表空间的ha表;
2) 使用 vpd(Virtual Private Database)的表,删除不进入回收站
XX@ORA11GR2>create table vpd_tab(id number(5)) tablespace users;
Table created.
XX@ORA11GR2>insert into vpd_tab values(1);
1 row created.
XX@ORA11GR2>insert into vpd_tab values(100);
1 row created.
XX@ORA11GR2>commit;
Commit complete.
XX@ORA11GR2>create or replace function fun_vpd_tab_lq(p_schema in varchar2,p_object in varchar2)
2 return varchar2 is
3 begin
4 return 'id<100';
5 end;
6 /
Function created.
XX@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>begin
2 dbms_rls.add_policy (object_schema => 'xx',
3 object_name => 'vpd_tab',
4 policy_name => 'p_vpd_tab_lq',
5 function_schema => 'xx',
6 policy_function => 'fun_vpd_tab_lq');
7 end;
8 /
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select * from xx.vpd_tab;
ID
----------
1
100
SYS@ORA11GR2>conn xx/oracle
Connected.
XX@ORA11GR2>select * from vpd_tab;
ID
----------
1
XX@ORA11GR2>select table_name,tablespace_name from user_tables where table_name='VPD_TAB';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
VPD_TAB USERS
XX@ORA11GR2>drop table vpd_tab;
Table dropped.
——查询回收站:
XX@ORA11GR2>show recyclebin;
XX@ORA11GR2>
或者:
XX@ORA11GR2>select original_name,object_name,type,droptime from user_recyclebin;
no rows selected
回收站不适用vpd(Virtual Private Database)的表!!!!!!!!!!!!!!!!!!!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31397003/viewspace-2126606/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126606/