浅谈Oracle闪回删除表限制-原创手记-慕课网

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

SQL> conn /as sysdba

Connected.

SQL> create user test identified by "123456" default tablespace users account unlock;

User created.

SQL> grant resource,connect to test;

Grant succeeded.

SQL> show parameter recyclebin;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string on

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

Connected.

SQL> create table drop_1 (a number) tablespace system;

Table created.

SQL> insert into drop_1 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> create table drop_2 as select * 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> drop table drop_1;

Table dropped.

SQL> drop table drop_2;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

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;

USER is "SYS"

SQL> drop table 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 DROP TIME

---------------- ------------------------------ ------------ -------------------

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> create table drop_3 tablespace users as select * from test.drop_2;

Table created.

SQL> drop table drop_3;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------- ------------------------------ ------------ -------------------

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> create table drop_audit (os_name varchar2(20),db_name varchar2(20),ip_addr char(10),select_time timestamp);

Table created.

SQL> create or replace procedure proc_drop2_select_audit

2 (object_shema varchar2,object_name varchar2,policy_name varchar2)

3 is

4 begin

5 insert into 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> insert into drop_2 values (2);

1 row created.

SQL> insert into drop_2 values (3);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from drop_2;

----------

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;

no rows selected

SQL> drop table drop_2;

Table dropped.

SQL> show recyclebin;

SQL> select * from user_recyclebin;

no rows selected

四：测试表存在VPD的情况下，是否可以成功闪回删除

4.1 创建2个测试用户test1,test2,并赋予相应的权限

SQL> conn /as sysdba

Connected.

SQL> create user test1 identified by "123456" default tablespace users account unlock;

User created.

SQL> create user test2 identified by "123456" default tablespace users account unlock;

User created.

SQL> grant connect,resource to test1;

Grant succeeded.

SQL> grant connect,resource to test2;

Grant succeeded.

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

SQL> conn test/123456

Connected.

SQL> create table drop_vpd (name varchar2(20),id number,salary number);

Table created.

SQL> insert into drop_vpd values ('test1',1,10000);

1 row created.

SQL> insert into drop_vpd values ('test2',2,20000);

1 row created.

SQL> insert into 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> create or replace function 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> grant select on 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;

no rows selected

SQL> drop table drop_vpd;

Table dropped.

SQL> select * from user_recyclebin;

no rows selected

限制flashbackvpdOracle