Restrictions on Oracle flashback table Deletion

Oracle 10 Gb has provided the recycle bin function similar to windows. When you delete a table, it is not directly deleted, but moved to the recycle bin. If you need to retrieve the original table from the recycle bin, you can use flashback to delete a table to quickly retrieve the deleted table without importing the original table from the backup! However, this recycle bin function is also prerequisite. You can not use flashback to delete tables under any circumstances. In summary, in 10 Gb, tables cannot be deleted using flashback in the following scenarios, if there is any improvement on the 11g, you can refer to the method in this article to test it! By the way, the recycle bin adopts a fifo, first-in-first-out mechanism! For example, if there are two tables with the same name in the recycle bin, you can find the table that was first flashed back Based on the sequence of the deletion time. Therefore, the "rename to" option is provided for the "flashback Delete table" command;

1: the recycle bin function is not enabled (this does not need to be tested)
2: The table's storage tablespace cannot be system
3: The purge parameter cannot be included when the table is deleted.
4: when there is space pressure
5: fine-grained audit is enabled on the table
6: VPD is enabled for the table.

I. test whether the table space is stored as system flash-back Deletion
1.1 create a test user and grant corresponding permissions to enable the database recycle bin function.

 
 

  
  
[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> 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 use the test user to create two tables, one of which is stored in the system tablespace

 
 

  
  
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 Delete two tables without the purge parameter. Check that the recycle bin only has the drop_2 table. The table's storage tablespace is users, as a result, it is proved that tables stored in the tablespace as system cannot be flashed back and deleted.

 
 

  
  
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 test whether the drop_2 table can be deleted with sys user.

 
 

  
  
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 
 
 

1 2 3 Next Page