Saturday, 1 December 2012

Recyclebin/Flashback


We have a feature in Oracle called flashback/recyclebin. If it is enabled, any table dropped moves to oracle's recyclebin. That means you can recover that table at any point later if someone has accidentally dropped the table. It is similar to windows recyclebin where in if file is deleted it can be recovered from recyclebin.




Recyclebin


Oracle has provided two views user_recyclebin and dba_recyclebin for this purpose but for ease we also have recyclebin synonym which points to user_recyclebin.

By default recyclebin parameter is "ON" in Oracle 10g and later, but you can disable it if you don't want to use this feature. We will discuss this later on in this article.

Let's understand this feature with an example. 


Create a table as shown, we have used serial_no to maintain the different versions of the same table. This point will become clear later on. Insert a row in the table with serial_no as "1".

SQL> create table test (serial_no number,c_date date);
Table created.

SQL> insert into test values (1,sysdate);
1 row created.


If we query user_tables and tab, we find that our new object has been created.Our recyclebin view is still empty as we have only created the table and nothing is dropped yet.

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected

Now we will drop the table and see what happens. There is no row in user_tables and it was expected as we have dropped the table. There is a row in recyclebin view which was also expected as the table that we have dropped has moved to recyclebin. The object_name represents the new name of the dropped object, original_name represents the name of the original object which was dropped, type tells us the type of object dropped, can_undrop tells us whether the object can be undropped or not, can_purge tells us that whether object can be deleted from the recylebin or not. 

SQL> drop table test;
Table dropped.

SQL> select table_name,tablespace_name from user_tables;
no rows selected

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TABLE

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE       CU  CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES

Now we will again create the table test and insert a row into it. This time serial_no is 2. If we closely look at the view "TAB" we find that it shows two entries one for newly created object and another for dropped table object hence we can conclude that TAB view is made from two views "user_tables" and "user_recyclebin".

SQL> create table test (serial_no number,c_date date);
Table created.

SQL> insert into test values (2,sysdate);
1 row created.

SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TABLE
TEST                           TABLE

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE       CU  CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES

We will drop this table again and query the recyclebin view. Now there are two entries one for serial_no "1" and another for serial_no "2" which is shown in the query below. Make sure to use ' " ' (double quotes) while querying tables in recyclebin. 

SQL> drop table test;
Table dropped.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE       CU  CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7o6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES

SQL> select * from "BIN$z3VTkO7n6RjgQKjAMgRUvw==$0";
 SERIAL_NO C_DATE
---------- ---------
         1 27-NOV-12

SQL> select * from "BIN$z3VTkO7o6RjgQKjAMgRUvw==$0";
 SERIAL_NO C_DATE
---------- ---------
         2 27-NOV-12

Flashback

Flashback is used to recover dropped objects from recyclebin. Flashback recovers the most recent dropped object from recyclebin. In our case also test table with serial_no "2" was the most recent one and hence it was recovered. The first copy of test table is still in the recyclebin. If you want to use that copy you need to rename test to test_tmp and then execute flashback query again. Or you can also use "flashback table "BIN...." to before drop" command directly. 

SQL> flashback table test to before drop;
Flashback complete.

SQL> select * from test;
 SERIAL_NO C_DATE
---------- ---------
         2 27-NOV-12

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE       CU  CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES


Purging

Purging is used to delete objects from recyclebin. You can delete individual objects by using command "purge table object_name". To delete all the objects in recyclebin at once you can use command "purge recyclebin". Make sure you purge objects from the recyclebin periodically as these objects are still occupying the same segments and eating up your space. You can also disable the recyclebin option as shown in next section.

SQL> purge table "BIN$z3VTkO7n6RjgQKjAMgRUvw==$0";
Table purged.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected

SQL> purge recyclebin;
Recyclebin purged.

If the purge clause is used with drop command then object does not move to recyclebin, it is deleted permanently and can't be recovered from flashback.

SQL> drop table test purge;
Table dropped.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected

No data in recyclebin as table is purged with drop. It is similar to windows command (Shift + Delete).

Recyclebin Parameter

There might be a situation wherein you don't want to use this feature. You can do this simply by setting recyclebin parameter "OFF". Now it becomes equivalent to "drop table purge" command. You can set this parameter at both session and system level.

SQL> create table test (serial_no number,c_date date);                                             
Table created.

SQL> insert into test values (3,sysdate);
1 row created.

SQL> alter session set recyclebin=off;
Session altered.

SQL> drop table test;
Table dropped.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected

Impact on Dependent Objects


Here we will see what happens to the dependent objects of dropped objects. We will create index along with the table and then drop the table. On dropping, index will also be dropped.

SQL> create table test (serial_no number,c_date date);
Table created.

SQL> create index test_idx on test (serial_no);
Index created.

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

SQL> select index_name, table_name from user_indexes;
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TEST_IDX                       TEST

SQL> drop table test;
Table dropped.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE       CU  CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 TEST_IDX           INDEX      NO  YES
BIN$z3VTkO7q6RjgQKjAMgRUvw==$0 TEST               TABLE      YES YES

As you can see clearly in recylebin that there are two entries, one for the table and another for the index. Once you issue a flashback query both the objects are restored.

SQL> flashback table test to before drop;
Flashback complete.

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

SQL> select index_name, table_name from user_indexes;
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 TEST


One thing to notice here is that name of the original index is lost as can be clearly seen from user_indexes view. So keep this thing in mind while restoring tables with index and make sure to rename your index so as to give them meaningful name.  


Note: If an index is dropped without the table then it doesn't move to recyclebin, it is purged directly.

Can_Undrop and Can_Purge

Now we will see the use "can_undrop" and "can_purge" columns.Can_undrop column is "NO" for row corresponding to index, which means that this object can not be undropped individually as this is a dependent object. But you can purge this object from recyclebin.

SQL> drop table test;
Table dropped.

SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME                    ORIGINAL_NAME                  TYPE       CU  CP
------------------------------ ------------------------------ ---------- --- ---
BIN$z3VTkO7s6RjgQKjAMgRUvw==$0 TEST                           TABLE      YES YES
BIN$z3VTkO7r6RjgQKjAMgRUvw==$1 BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 INDEX      NO  YES                         

SQL> purge index "BIN$z3VTkO7r6RjgQKjAMgRUvw==$1";
Index purged.


If we flashback now only the table is restored as we have already deleted the index from recyclebin. We will have to create the index manually.

SQL> flashback table test to before drop;
Flashback complete.

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST                           USERS

SQL> select index_name, table_name from user_indexes;
no rows selected

No comments:

Post a Comment

back to top