Flashback Table

Applies to: Database Management Software Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2

Oracle database introduced a new feature Recyclebin. It is same as we have in windows, i.e. whatever we delete a file it goes to recycle bin. Let's see how it works in Oracle Database.

Let have a table with some data.

SQL> select count(*) from stats_table1;

  COUNT(*)
----------
        12

SQL> drop table stats_table1;

Table dropped.

Table is present with different name by some naming convention in recyclebin. All objects on table like indexes, triggers are also dropped and renamed with same naming convention. Dependent objects on this table like procedure and functions goes invalid.

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
STATS_TABLE1     BIN$1MB8W2qCqd3gQAB/AQAMYQ==$0 TABLE        2013-02-02:07:26:37

To get back this table use below command.

SQL> flashback table stats_table1 to before drop;

Flashback complete.

Now, table is back.

SQL> select count(*) from stats_table1;

  COUNT(*)
----------
        12

If, you want to delete table permanently use purge keyword with drop.

SQL> drop table stats_table1 purge;

Table dropped.

Now, Table is not available in recyclebin.

SQL> show recyclebin;

Permanent Disable Recycle Bin.

In Database Management Software Oracle 10gR1 to permanent disable the behavior of recycling, there is an underscore parameter
"_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm like '%recyclebin%'
ORDER BY a.ksppinm;

         Parameter                            Value                   Default?
---------------------------- ---------------------------------------- --------
_recyclebin                  TRUE                                     TRUE

From SYSDBA user:

SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

On Database Management Software 10gR2 and higher; recyclebin is a initialization parameter and by default its ON. Use below command to permanent disable this.

From SYSDBA user:

SQL> ALTER SESSION SET recyclebin = OFF;

Session altered.

No comments:

Post a Comment