浅谈Oracle闪回删去表约束51CTO博客 - AG环亚娱乐

浅谈Oracle闪回删去表约束51CTO博客

2019年03月10日13时44分34秒 | 作者: 白容 | 标签: 删去,闪回,用户 | 浏览: 1964

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 
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表AG环亚娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章