浅谈Oracle闪回删除表限制
时间:2015-12-29 22:25 来源:linux.it.net.cn 作者:IT
Oracle 10g开始提供了类似windows系统的回收站功能，用户在删除表的时候会不是直接删除，而是移动到回收站中，如果需要从回收站中取回原来的表，可以使用闪回删除表的特性，迅速的找回被删除的表，而不需要从备份中导入原有的表！但这个回收站功能也是有前提的，不是在任何情况下都可以使用闪回删除表特性，总结下，在10g中，下面几种场景表不能flashback删除，至于11g是否有改进，感兴趣的朋友可以参照本文的方法进行测试下！顺带说下，回收站采取fifo，先进先出的机制！例如在回收站有两张同样名称的表，闪回删除的时候根据删除时间的先后顺序来觉得先闪回哪张表，因而闪回删除表命令提供了rename to选项；
1：回收站功能未开启(这个就不用测试了)
2：表的存储表空间不能为system
3: 表被删除的时候不能带purge参数
4：存在空间压力的时候
5：表上面启用了细粒度审计
6：表启用了VPD
一：测试表空间存储为system的闪回删除
1.1 创建测试用户，并赋予相应的权限，开启数据库的回收站功能
[oracle@dg53 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 16 16:48:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> createuser test identified by"123456"default tablespace users account unlock;
User created.
SQL> grant resource,connectto test;
Grant succeeded.
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
1.2 使用test用户新建2张表，一张存储在system表空间
Connected.
SQL> createtable drop_1 (a number) tablespace system;
Table created.
SQL> insertinto drop_1 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> createtable drop_2 asselect * from drop_1;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DROP_1 TABLE
DROP_2 TABLE
1.3 分别删除2张表，不带purge参数，查看回收站只存在drop_2表，该表的存储表空间为users,由此证明表空间存储为system的表是不可以被闪回删除的
SQL> droptable drop_1;
Table dropped.
SQL> droptable drop_2;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME
---------------- ------------------------------ ------------ -------------------
DROP_2 BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 TABLE 2012-02-16:16:53:36
SQL> select object_name,original_name from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 DROP_2
SQL> flashback table drop_2 to before drop;
Flashback complete.
1.4 测试使用sys用户删除drop_2表，是否可以成功闪回删除
SQL> conn /as sysdba
Connected.
SQL> show user;
USERis"SYS"
SQL> droptable test.drop_2;
Table dropped.
SQL> conn test/123456
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME
---------------- ------------------------------ ------------ -------------------
DROP_2 BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE 2012-02-16:17:06:54
SQL> flashback table drop_2 to before drop;
Flashback complete
二：测试sys用户的表，存储表空间不为system情况下，是否可以成功闪回删除
之前在这里存在误区，以为凡是sys用户拥有的表就都不可以闪回删除
SQL> conn /as sysdba
Connected.
SQL> createtable drop_3 tablespace users asselect * from test.drop_2;
Table created.
SQL> droptable drop_3;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME
---------------- ------------------------------ ------------ -------------------
DROP_3 BIN$uRIyxUtYT83gQKjANQEekA==$0 TABLE 2012-02-16:17:10:24
三：测试表存在细粒度审计的情况下，是否可以成功闪回删除
3.1 创建记录审计内容的表和审计所需要的的存储过程
SQL> conn test/123456
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DROP_2 TABLE
SQL> createtable drop_audit (os_name varchar2(20),db_name varchar2(20),ip_addr char(10),select_time timestamp);
Table created.
SQL> createorreplaceprocedure proc_drop2_select_audit
2 (object_shema varchar2,object_name varchar2,policy_name varchar2)
3 is
4 begin
5 insertinto drop_audit values (sys_context('userenv','os_user'),user,sys_context('userenv','ip_address'),sysdate);
6* end proc_drop2_select_audit;
Procedure created.
SQL> select status from user_objects where object_name='PROC_DROP2_SELECT_AUDIT';
STATUS
-------
VALID
3.2 使用sys用户添加审计策略，并验证其是否生效
SQL> conn /as sysdba
Connected.
SQL> begin
2 dbms_fga.add_policy (
3 object_schema => 'TEST',
4 object_name => 'DROP_2',
5 policy_name => 'POLICY_1',
6 audit_condition => 'A=1',
7 audit_column => 'A',
8 handler_schema => 'TEST',
9 handler_module => 'PROC_DROP2_SELECT_AUDIT',
10 enable => TRUE,
11 statement_types => 'SELECT' );
12* end;
PL/SQL procedure successfully completed.
SQL> conn test/123456
Connected.
SQL> insertinto drop_2 values (2);
1 row created.
SQL> insertinto drop_2 values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from drop_2;
A
----------
1
2
3
SQL> select * from drop_audit;
OS_NAME DB_NAME IP_ADDR SELECT_TIME
---------- -------------------- ---------- ------------------------------
Oracle TEST 17-FEB-12 11.35.25.000000 AM
3.3 删除带细粒度审计的表，验证！
SQL> show recyclebin;
SQL> select * from user_recyclebin;
norows selected
SQL> droptable drop_2;
Table dropped.
SQL> show recyclebin;
SQL> select * from user_recyclebin;
norows selected
四：测试表存在VPD的情况下，是否可以成功闪回删除
4.1 创建2个测试用户test1,test2,并赋予相应的权限
SQL> conn /as sysdba
Connected.
SQL> createuser test1 identified by"123456"default tablespace users account unlock;
User created.
SQL> createuser test2 identified by"123456"default tablespace users account unlock;
User created.
SQL> grantconnect,resource to test1;
Grant succeeded.
SQL> grantconnect,resource to test2;
Grant succeeded.
4.2 使用test用户建表和VPD需要使用到的函数
SQL> conn test/123456
Connected.
SQL> createtable drop_vpd (name varchar2(20),id number,salary number);
Table created.
SQL> insertinto drop_vpd values ('test1',1,10000);
1 row created.
SQL> insertinto drop_vpd values ('test2',2,20000);
1 row created.
SQL> insertinto drop_vpd values ('test',3,30000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from drop_vpd;
NAME ID SALARY
-------------------- ---------- ----------
test1 1 10000
test2 2 20000
test 3 30000
SQL> createorreplacefunction func_drop_vpd
2 (owner varchar2,objectname varchar2)
3 return varchar2
4 is
5 where_cluase varchar2(4000);
6 begin
7 where_cluase := 'name=sys_context(''userenv'',''session_user'')';
8 return where_cluase;
9* end func_drop_vpd;
Function created.
SQL> select status from user_objects where object_name='FUNC_DROP_VPD';
STATUS
-------
VALID
SQL> grantselecton drop_vpd to test1,test2;
Grant succeeded.
4.3 使用sys用户添加VPD策略，并测试
SQL> conn /as sysdba
Connected.
SQL> BEGIN
2 dbms_rls.add_policy(object_schema => 'test',
3 object_name => 'drop_vpd',
4 policy_name => 'policy2',
5 function_schema =>'test',
6 policy_function => 'func_drop_vpd',
7 statement_types =>'select',
8 sec_relevant_cols=>'salary');
9* END;
PL/SQL procedure successfully completed.
SQL> select object_owner,sel,ins from dba_policies where object_name='DROP_VPD';
OBJECT_OWNER SEL INS
------------------------------ --- ---
TEST YES NO
4.4 删除启用VPD的表，验证是否可以使用闪回删除表
SQL> conn test/123456
Connected.
SQL> select * from user_recyclebin;
norows selected
SQL> droptable drop_vpd;
Table dropped.
SQL> select * from user_recyclebin;
norows selected
(责任编辑：IT)
| Oracle 10g开始提供了类似windows系统的回收站功能，用户在删除表的时候会不是直接删除，而是移动到回收站中，如果需要从回收站中取回原来的表，可以使用闪回删除表的特性，迅速的找回被删除的表，而不需要从备份中导入原有的表！但这个回收站功能也是有前提的，不是在任何情况下都可以使用闪回删除表特性，总结下，在10g中，下面几种场景表不能flashback删除，至于11g是否有改进，感兴趣的朋友可以参照本文的方法进行测试下！顺带说下，回收站采取fifo，先进先出的机制！例如在回收站有两张同样名称的表，闪回删除的时候根据删除时间的先后顺序来觉得先闪回哪张表，因而闪回删除表命令提供了rename to选项； 1：回收站功能未开启(这个就不用测试了) 2：表的存储表空间不能为system 3: 表被删除的时候不能带purge参数 4：存在空间压力的时候 5：表上面启用了细粒度审计 6：表启用了VPD 一：测试表空间存储为system的闪回删除 1.1 创建测试用户，并赋予相应的权限，开启数据库的回收站功能 [oracle@dg53 ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 16 16:48:44 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> createuser test identified by"123456"default tablespace users account unlock; User created. SQL> grant resource,connectto test; Grant succeeded. SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on 1.2 使用test用户新建2张表，一张存储在system表空间 Connected. SQL> createtable drop_1 (a number) tablespace system; Table created. SQL> insertinto drop_1 values (1); 1 row created. SQL> commit; Commit complete. SQL> createtable drop_2 asselect * from drop_1; Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DROP_1 TABLE DROP_2 TABLE 1.3 分别删除2张表，不带purge参数，查看回收站只存在drop_2表，该表的存储表空间为users,由此证明表空间存储为system的表是不可以被闪回删除的 SQL> droptable drop_1; Table dropped. SQL> droptable drop_2; Table dropped. SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME ---------------- ------------------------------ ------------ ------------------- DROP_2 BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 TABLE 2012-02-16:16:53:36 SQL> select object_name,original_name from user_recyclebin; OBJECT_NAME ORIGINAL_NAME ------------------------------ -------------------------------- BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 DROP_2 SQL> flashback table drop_2 to before drop; Flashback complete. 1.4 测试使用sys用户删除drop_2表，是否可以成功闪回删除 SQL> conn /as sysdba Connected. SQL> show user; USERis"SYS" SQL> droptable test.drop_2; Table dropped. SQL> conn test/123456 Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME ---------------- ------------------------------ ------------ ------------------- DROP_2 BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE 2012-02-16:17:06:54 SQL> flashback table drop_2 to before drop; Flashback complete 二：测试sys用户的表，存储表空间不为system情况下，是否可以成功闪回删除 之前在这里存在误区，以为凡是sys用户拥有的表就都不可以闪回删除 SQL> conn /as sysdba Connected. SQL> createtable drop_3 tablespace users asselect * from test.drop_2; Table created. SQL> droptable drop_3; Table dropped. SQL> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROPTIME ---------------- ------------------------------ ------------ ------------------- DROP_3 BIN$uRIyxUtYT83gQKjANQEekA==$0 TABLE 2012-02-16:17:10:24 三：测试表存在细粒度审计的情况下，是否可以成功闪回删除 3.1 创建记录审计内容的表和审计所需要的的存储过程 SQL> conn test/123456 Connected. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DROP_2 TABLE SQL> createtable drop_audit (os_name varchar2(20),db_name varchar2(20),ip_addr char(10),select_time timestamp); Table created. SQL> createorreplaceprocedure proc_drop2_select_audit 2 (object_shema varchar2,object_name varchar2,policy_name varchar2) 3 is 4 begin 5 insertinto drop_audit values (sys_context('userenv','os_user'),user,sys_context('userenv','ip_address'),sysdate); 6* end proc_drop2_select_audit; Procedure created. SQL> select status from user_objects where object_name='PROC_DROP2_SELECT_AUDIT'; STATUS ------- VALID 3.2 使用sys用户添加审计策略，并验证其是否生效 SQL> conn /as sysdba Connected. SQL> begin 2 dbms_fga.add_policy ( 3 object_schema => 'TEST', 4 object_name => 'DROP_2', 5 policy_name => 'POLICY_1', 6 audit_condition => 'A=1', 7 audit_column => 'A', 8 handler_schema => 'TEST', 9 handler_module => 'PROC_DROP2_SELECT_AUDIT', 10 enable => TRUE, 11 statement_types => 'SELECT' ); 12* end; PL/SQL procedure successfully completed. SQL> conn test/123456 Connected. SQL> insertinto drop_2 values (2); 1 row created. SQL> insertinto drop_2 values (3); 1 row created. SQL> commit; Commit complete. SQL> select * from drop_2; A ---------- 1 2 3 SQL> select * from drop_audit; OS_NAME DB_NAME IP_ADDR SELECT_TIME ---------- -------------------- ---------- ------------------------------ Oracle TEST 17-FEB-12 11.35.25.000000 AM 3.3 删除带细粒度审计的表，验证！ SQL> show recyclebin; SQL> select * from user_recyclebin; norows selected SQL> droptable drop_2; Table dropped. SQL> show recyclebin; SQL> select * from user_recyclebin; norows selected 四：测试表存在VPD的情况下，是否可以成功闪回删除 4.1 创建2个测试用户test1,test2,并赋予相应的权限 SQL> conn /as sysdba Connected. SQL> createuser test1 identified by"123456"default tablespace users account unlock; User created. SQL> createuser test2 identified by"123456"default tablespace users account unlock; User created. SQL> grantconnect,resource to test1; Grant succeeded. SQL> grantconnect,resource to test2; Grant succeeded. 4.2 使用test用户建表和VPD需要使用到的函数 SQL> conn test/123456 Connected. SQL> createtable drop_vpd (name varchar2(20),id number,salary number); Table created. SQL> insertinto drop_vpd values ('test1',1,10000); 1 row created. SQL> insertinto drop_vpd values ('test2',2,20000); 1 row created. SQL> insertinto drop_vpd values ('test',3,30000); 1 row created. SQL> commit; Commit complete. SQL> select * from drop_vpd; NAME ID SALARY -------------------- ---------- ---------- test1 1 10000 test2 2 20000 test 3 30000 SQL> createorreplacefunction func_drop_vpd 2 (owner varchar2,objectname varchar2) 3 return varchar2 4 is 5 where_cluase varchar2(4000); 6 begin 7 where_cluase := 'name=sys_context(''userenv'',''session_user'')'; 8 return where_cluase; 9* end func_drop_vpd; Function created. SQL> select status from user_objects where object_name='FUNC_DROP_VPD'; STATUS ------- VALID SQL> grantselecton drop_vpd to test1,test2; Grant succeeded. 4.3 使用sys用户添加VPD策略，并测试 SQL> conn /as sysdba Connected. SQL> BEGIN 2 dbms_rls.add_policy(object_schema => 'test', 3 object_name => 'drop_vpd', 4 policy_name => 'policy2', 5 function_schema =>'test', 6 policy_function => 'func_drop_vpd', 7 statement_types =>'select', 8 sec_relevant_cols=>'salary'); 9* END; PL/SQL procedure successfully completed. SQL> select object_owner,sel,ins from dba_policies where object_name='DROP_VPD'; OBJECT_OWNER SEL INS ------------------------------ --- --- TEST YES NO 4.4 删除启用VPD的表，验证是否可以使用闪回删除表 SQL> conn test/123456 Connected. SQL> select * from user_recyclebin; norows selected SQL> droptable drop_vpd; Table dropped. SQL> select * from user_recyclebin; norows selected (责任编辑：IT) |