Flashback Technology :#02

Performing a Flashback Drop Operation

In Oracle Ver. 10g  Oracle introduced the concept of Recycle Bin i.e. whatever tables you drop the database does not immediately remove the space used by table. Instead, the table is renamed and placed in Recycle Bin. The FLASHBACK TABLE…BEFORE DROP command will restore the table.
Oracle renames dropped and their associated objects, giving them system-generated recyclebin names that begin with BIN$.

BIN$unique_id$version
where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases 
• version is a version number assigned by the database

This feature is not dependent on UNDO TABLESPACE so UNDO_RETENTION parameter has no impact on this feature.

To Recover Drop tables the RECYCLEBIN parameter must be turned ON, otherwise you will not be able to recover drop tables

SQL> SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

VALUE
---------------
on

SQL>

if the Value Is “on” then recyclebin feature is enabled for the Database. 
If the Value is “off” the recyclebin feature is disabled.

SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;

Show the Contents in RECYCLEBIN

SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;
or
SQL> SELECT * FROM DBA_RECYCLEBIN;


Restore the Objects:
User can restore the Dropped tables by issuing the following commands,The following Commands can be used to restore the dropped Objects.

SQL > FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z

SQL>
SQL> drop table test;

Table dropped.

SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Y6TCpiPVE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:06:54:40
SQL>

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> select * from user_recyclebin;

no rows selected

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z

SQL> 


 It is possible to restore the table in to different name by issuing the following SQL Command.

 SQL > FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New Table Name >>;

 FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

 SQL>  select * from test;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z
         3 A

SQL> drop table test;

Table dropped.

SQL> select * from test;
select * from test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Y6TCpiPWE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:06:59:46

SQL> FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO TEST1;

Flashback complete.

SQL> select * from test1;

        ID NAME
---------- --------------------------------------------------
         1 D
         2 Z
         3 A


 
Using the above statement, its possible to restore the various version of the table data if the table
is created and Dropped more than once.

While restoring system restores the table in Descending order.

SQL> show recyclebin
SQL>
SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL> INSERT INTO TEST_RBIN VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> CREATE TABLE TEST_RBIN (COL1 NUMBER);

Table created.

SQL>  INSERT INTO TEST_RBIN VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST_RBIN;

Table dropped.

SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST_RBIN        BIN$Y6TCpiPZE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:05:02
TEST_RBIN        BIN$Y6TCpiPYE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:04:22
TEST_RBIN        BIN$Y6TCpiPXE1jgVQAAAAAAAQ==$0 TABLE        2018-01-26:07:03:45
SQL>  FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

Flashback complete.

SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN2;

Flashback complete.

SQL> FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN3;

Flashback complete.

SQL> SELECT * FROM TEST_RBIN1;

      COL1
----------
         3

SQL> SELECT * FROM TEST_RBIN2;

      COL1
----------
         2

SQL> SELECT * FROM TEST_RBIN3;

      COL1
----------
         1

SQL>



Purging Objects from Recycle Bin

f you want to recover the space used by a dropped table give the following command

SQL> purge table emp;

If you want  to purge objects of  logon user  give the following command

SQL> purge recycle bin;

If you want to recover space for dropped objects of a particular tablespace give the command

SQL> purge tablespace hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

SQL> PURGE TABLESPACE hr USER scott;

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

SQL> PURGE DBA_RECYCLEBIN;

To view the contents of Recycle Bin give the following command

SQL> show recycle bin;

Permanently Dropping Tables
If you want to permanently drop tables without putting it into Recycle Bin, drop tables with purge command like this

SQL> drop table emp purge;

This will drop the table permanently and it cannot be restored.




if table have indexes



SQL> drop table test;

Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------------------------
BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 BIN$Y6TCpiPaE1jgVQAAAAAAAQ==$0   INDEX
BIN$Y6TCpiPdE1jgVQAAAAAAAQ==$0 TEST                             TABLE

SQL>
SQL> flashback table test to before drop;

Flashback complete.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from user_recyclebin;

no rows selected

SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';

OWNER                          INDEX_NAME                     TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT                          BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1 TEST       VALID

SQL> alter index "BIN$Y6TCpiPcE1jgVQAAAAAAAQ==$1" rename to test_id_idx;

Index altered.

SQL> select owner,index_name,table_name,status from dba_indexes where owner='DUCAT';

OWNER                          INDEX_NAME                     TABLE_NAME STATUS
------------------------------ ------------------------------ ---------- --------
DUCAT                          TEST_ID_IDX                    TEST       VALID

SQL>

Related Views

• RECYCLEBIN$ (base table)
• DBA_RECYCLEBIN
• USER_RECYCLEBIN
• RECYCLEBIN (synonym for USER_RECYCLEBIN)