浅谈Oracle闪回删除表限制

Oracle 10g开始提供了类似windows系统的回收站功能，用户在删除表的时候会不是直接删除，而是移动到回收站中，如果需要从回收站中取回原来的表，可以使用闪回删除表的特性，迅速的找回被删除的表，而不需要从备份中导入原有的表！但这个回收站功能也是有前提的，不是在任何情况下都可以使用闪回删除表特性，总结下，在10g中，下面几种场景表不能flashback删除，至于11g是否有改进，感兴趣的朋友可以参照本文的方法进行测试下！顺带说下，回收站采取fifo，先进先出的机制！例如在回收站有两张同样名称的表，闪回删除的时候根据删除时间的先后顺序来觉得先闪回哪张表，因而闪回删除表命令提供了rename to选项；

1：回收站功能未开启(这个就不用测试了)
2：表的存储表空间不能为system
3: 表被删除的时候不能带purge参数
4：存在空间压力的时候
5：表上面启用了细粒度审计
6：表启用了VPD

一：测试表空间存储为system的闪回删除
1.1 创建测试用户，并赋予相应的权限，开启数据库的回收站功能

1. [oracle@dg53 ~]$ sqlplus /nolog  
2. SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 16 16:48:44 2012  
3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.  
4.  
5. SQL> conn /as sysdba  
6. Connected.  
7.  
8. SQL> create user test identified by "123456" default tablespace users account unlock;  
9. User created.  
10.  
11. SQL> grant resource,connect to test;  
12. Grant succeeded.  
13.  
14. SQL> show parameter recyclebin;  
15.  
16. NAME                                 TYPE        VALUE  
17. ------------------------------------ ----------- ------------------------------  
18. recyclebin                           string      on 

1.2 使用test用户新建2张表，一张存储在system表空间

1. Connected.  
2. SQL> create table drop_1 (a number) tablespace system;  
3. Table created.  
4.  
5. SQL> insert into drop_1 values (1);  
6. 1 row created.  
7.  
8. SQL> commit;  
9. Commit complete.  
10.  
11. SQL> create table drop_2 as select * from drop_1;  
12. Table created.  
13.  
14. SQL> select * from tab;  
15.  
16. TNAME                          TABTYPE  CLUSTERID  
17. ------------------------------ ------- ----------  
18. DROP_1                         TABLE 
19. DROP_2                         TABLE 

1.3 分别删除2张表，不带purge参数，查看回收站只存在drop_2表，该表的存储表空间为users,由此证明表空间存储为system的表是不可以被闪回删除的

1. SQL> drop table drop_1;  
2. Table dropped.  
3.  
4. SQL> drop table drop_2;  
5. Table dropped.  
6.  
7. SQL> show recyclebin;  
8. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME 
9. ---------------- ------------------------------ ------------ -------------------  
10. DROP_2           BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 TABLE        2012-02-16:16:53:36  
11.  
12. SQL> select object_name,original_name from user_recyclebin;  
13.  
14. OBJECT_NAME                    ORIGINAL_NAME  
15. ------------------------------ --------------------------------  
16. BIN$uRD2vL3ZVNjgQKjANQEaNg==$0 DROP_2  
17.  
18. SQL> flashback table drop_2 to before drop;  
19. Flashback complete. 

1.4 测试使用sys用户删除drop_2表，是否可以成功闪回删除

1. SQL> conn /as sysdba  
2. Connected.  
3. SQL> show user;  
4. USER is "SYS" 
5. SQL> drop table test.drop_2;  
6. Table dropped.  
7.  
8. SQL> conn test/123456  
9. Connected.  
10. SQL> select * from tab;  
11.  
12. TNAME                          TABTYPE  CLUSTERID  
13. ------------------------------ ------- ----------  
14. BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE 
15.  
16. SQL> show recyclebin;  
17. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME 
18. ---------------- ------------------------------ ------------ -------------------  
19. DROP_2           BIN$uRImQA9UYD7gQKjANQEdrg==$0 TABLE        2012-02-16:17:06:54  
20.  
21. SQL> flashback table drop_2 to before drop;  
22. Flashback complete 

二：测试sys用户的表，存储表空间不为system情况下，是否可以成功闪回删除

之前在这里存在误区，以为凡是sys用户拥有的表就都不可以闪回删除

1. SQL> conn /as sysdba  
2. Connected.  
3. SQL> create table drop_3 tablespace users as select * from test.drop_2;  
4. Table created.  
5.  
6. SQL> drop table drop_3;  
7. Table dropped.  
8.  
9. SQL> show recyclebin;  
10. ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME 
11. ---------------- ------------------------------ ------------ -------------------  
12. DROP_3           BIN$uRIyxUtYT83gQKjANQEekA==$0 TABLE        2012-02-16:17:10:24 

三：测试表存在细粒度审计的情况下，是否可以成功闪回删除
3.1 创建记录审计内容的表和审计所需要的的存储过程

1. SQL> conn test/123456  
2. Connected.  
3. SQL> select * from tab;  
4.  
5. TNAME                          TABTYPE  CLUSTERID  
6. ------------------------------ ------- ----------  
7. DROP_2                         TABLE 
8.  
9. SQL> create table drop_audit (os_name varchar2(20),db_name varchar2(20),ip_addr char(10),select_time timestamp);    
10. Table created.  
11.  
12. SQL> create or replace procedure proc_drop2_select_audit  
13.   2  (object_shema varchar2,object_name varchar2,policy_name varchar2)  
14.   3  is 
15.   4  begin 
16.   5     insert into drop_audit values (sys_context('userenv','os_user'),user,sys_context('userenv','ip_address'),sysdate);  
17.   6* end proc_drop2_select_audit;  
18. Procedure created.  
19.  
20. SQL> select status from user_objects where object_name='PROC_DROP2_SELECT_AUDIT';  
21.  
22. STATUS  
23. -------  
24. VALID 

3.2 使用sys用户添加审计策略，并验证其是否生效

1. SQL> conn /as sysdba  
2. Connected.  
3.  
4. SQL> begin 
5.   2      dbms_fga.add_policy (  
6.   3      object_schema   => 'TEST',  
7.   4      object_name     => 'DROP_2',  
8.   5      policy_name     => 'POLICY_1',  
9.   6      audit_condition => 'A=1',  
10.   7      audit_column    => 'A',  
11.   8      handler_schema  => 'TEST',  
12.   9      handler_module  => 'PROC_DROP2_SELECT_AUDIT',  
13.  10      enable          => TRUE,  
14.  11      statement_types => 'SELECT' );  
15.  12* end;  
16. PL/SQL procedure successfully completed.  
17.  
18. SQL> conn test/123456  
19. Connected.  
20. SQL> insert into drop_2 values (2);  
21. 1 row created.  
22.  
23. SQL> insert into drop_2 values (3);  
24. 1 row created.  
25.  
26. SQL> commit;  
27. Commit complete.  
28.  
29. SQL> select * from drop_2;  
30.  
31.          A  
32. ----------  
33.          1  
34.          2  
35.          3  
36.  
37. SQL> select * from drop_audit;  
38.  
39. OS_NAME    DB_NAME              IP_ADDR    SELECT_TIME  
40. ---------- -------------------- ---------- ------------------------------  
41. oracle     TEST                            17-FEB-12 11.35.25.000000 AM 

3.3 删除带细粒度审计的表，验证！

1. SQL> show recyclebin;  
2. SQL> select * from user_recyclebin;  
3. no rows selected  
4.  
5. SQL> drop table drop_2;  
6. Table dropped.  
7.  
8. SQL> show recyclebin;  
9. SQL> select * from user_recyclebin;  
10. no rows selected 

四：测试表存在VPD的情况下，是否可以成功闪回删除
4.1 创建2个测试用户test1,test2,并赋予相应的权限

1. SQL> conn /as sysdba  
2. Connected.  
3.  
4. SQL> create user test1 identified by "123456" default tablespace users account unlock;  
5. User created.  
6.  
7. SQL> create user test2 identified by "123456" default tablespace users account unlock;  
8. User created.  
9.  
10. SQL> grant connect,resource to test1;  
11. Grant succeeded.  
12.  
13. SQL> grant connect,resource to test2;  
14. Grant succeeded. 

4.2 使用test用户建表和VPD需要使用到的函数

1. SQL> conn test/123456  
2. Connected.  
3. SQL> create table drop_vpd (name varchar2(20),id number,salary number);  
4. Table created.  
5.  
6. SQL> insert into  drop_vpd values ('test1',1,10000);  
7. 1 row created.  
8.  
9. SQL> insert into  drop_vpd values ('test2',2,20000);  
10. 1 row created.  
11.  
12. SQL> insert into  drop_vpd values ('test',3,30000);  
13. 1 row created.  
14.  
15. SQL> commit;  
16. Commit complete.  
17.  
18. SQL> select * from drop_vpd;  
19.  
20. NAME                         ID     SALARY  
21. -------------------- ---------- ----------  
22. test1                         1      10000  
23. test2                         2      20000  
24. test                          3      30000  
25.  
26. SQL> create or replace function func_drop_vpd  
27.   2  (owner varchar2,objectname varchar2)  
28.   3  return varchar2  
29.   4  is 
30.   5  where_cluase varchar2(4000);  
31.   6  begin 
32.   7  where_cluase := 'name=sys_context(''userenv'',''session_user'')';  
33.   8  return where_cluase;  
34.   9* end func_drop_vpd;  
35. Function created.  
36.  
37. SQL> select status from user_objects where object_name='FUNC_DROP_VPD';  
38.  
39. STATUS  
40. -------  
41. VALID  
42.  
43. SQL> grant select on drop_vpd to test1,test2;  
44. Grant succeeded. 

4.3 使用sys用户添加VPD策略，并测试

1. SQL> conn /as sysdba  
2. Connected.  
3.  
4. SQL> BEGIN 
5.   2       dbms_rls.add_policy(object_schema => 'test',  
6.   3       object_name => 'drop_vpd',  
7.   4       policy_name => 'policy2',  
8.   5       function_schema =>'test',  
9.   6       policy_function => 'func_drop_vpd',  
10.   7       statement_types =>'select',  
11.   8       sec_relevant_cols=>'salary');  
12.   9* END;  
13.  
14. PL/SQL procedure successfully completed.  
15.  
16. SQL> select object_owner,sel,ins from dba_policies where object_name='DROP_VPD';   
17.  
18. OBJECT_OWNER                   SEL INS  
19. ------------------------------ --- ---  
20. TEST                           YES NO 

4.4 删除启用VPD的表，验证是否可以使用闪回删除表

1. SQL> conn test/123456  
2. Connected.  
3. SQL> select * from user_recyclebin;  
4. no rows selected  
5.  
6. SQL> drop table drop_vpd;  
7. Table dropped.  
8.  
9. SQL> select * from user_recyclebin;  
10. no rows selected