Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Recyclebin with Flashback Table in Oracle 10g

  1. #1
    Oracle Administrator
    Join Date
    Feb 2012
    Location
    New Delhi, India
    Posts
    53

    Recyclebin with Flashback Table in Oracle 10g

    Oracle 10g introduced a new feature of recycle bin. Before oracle 10g if you accidentally dropped a table, the only option to recover that table was incomplete database recovery provided that you have all the required backups. From 10g onwards a DROP TABLE statement doesn't actually drop the table but it puts the table into the database recycle bin from where the table can be recovered with one command.

    I will go through the recycle bin feature here and we will see how it works.

    $ sqlplus / as sysdba
    SQL> show parameter recyclebin;

    NAME TYPE VALUE ---------------------- ----------- ----------- recyclebin string ON /* Make sure the recyclebin is ON. */
    SQL> create tablespace ts_recyclebin 2 datafile '/d01/apps/oradata/oraxpo/ts_recyclebin01.dbf' 3 size 10m autoextend on next 1m maxsize 50m 4 extent management local 5 segment space management auto;
    Tablespace created.

    SQL> create user test_recyclebin identified by test; User created.
    SQL> grant connect , resource to test_recyclebin; Grant succeeded.
    SQL> alter user test_recyclebin 2 default tablespace ts_recyclebin 3 quota unlimited on ts_recyclebin;
    User altered.
    SQL>

    We have setup a tablespace of size 10 mega bytes and created a user with unlimited quota on the tablespace. Now login as the new user from another console leaving the sysdba session intact, and create a table of a significant size. Here I created a table of about 40,000 rows with a size around 5Mb.

    /* Open another console and login as user test_recyclebin. sysdba session is opened in the other console. */
    $ sqlplus test_recyclebin/test
    SQL> create table test_recyclebin 2 as 3 select * from all_objects; Table created.
    SQL>

    Come back to sysdba session and see how much of the tablespace is consumed by the table we just created.

    /* Back to sysdba session */
    SQL> select d.tablespace_name , 2 round(d.bytes/1024/1024) total_mb , 3 round(d.bytes/1024/1024)- 4 round(sum(f.bytes)/1024/1024) used_mb, 5 round(sum(f.bytes)/1024/1024) free_mb 6 from dba_data_files d, dba_free_space f 7 where f.file_id = d.file_id 8 and d.tablespace_name = 'TS_RECYCLEBIN' 9 group by d.tablespace_name , d.bytes;
    TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB ------------------------------ ---------- ---------- ---------- TS_RECYCLEBIN 10 5 5

    SQL>

    Lets now drop the table and see what happens to it.

    /* Back to session test_recyclebin */
    SQL> drop table test_recyclebin; Table dropped.
    SQL> select table_name 2 from user_tables 3 where table_name = 'TEST_RECYCLEBIN';
    no rows selected
    SQL> column type format a6 SQL> column original_name format a16
    SQL> column ts_name format a13 SQL> set lines 10000
    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;
    OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME ------------------------------ ---------------- ------ ------------- BIN$hBJrmLxc49DgQAB/AQAmMA==$0 TEST_RECYCLEBIN TABLE TS_RECYCLEBIN
    SQL>

    Please note that the table we dropped, is no more there in USER_TABLES but it can be seen in the USER_RECYCLEBIN.

    /* Back to sysdba session */
    SQL> select d.tablespace_name , 2 round(d.bytes/1024/1024) total_mb , 3 round(d.bytes/1024/1024)- 4 round(sum(f.bytes)/1024/1024) used_mb, 5 round(sum(f.bytes)/1024/1024) free_mb 6 from dba_data_files d, dba_free_space f 7 where f.file_id = d.file_id 8 and d.tablespace_name = 'TS_RECYCLEBIN' 9 group by d.tablespace_name , d.bytes;
    TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB ------------------------------ ---------- ---------- ---------- TS_RECYCLEBIN 10 0 10
    SQL>

    Here we can see that the space that was being used by the table is released. The used space is 0. As we saw in the recyclebin the table is still there but the tablespace is being shown empty.
    Now whats happening here is that Oracle actually rename the dropped table instead of actually dropping it and put a record in recyclebin against that table (e.g. in our case the new name of the table is "BIN$hBJrmLxc49DgQAB/AQAmMA==$0"). The table is still there in the same tablespace but the space used by the table is considered as free space now.

    /* Back to session test_recyclebin */
    SQL> flashback table test_recyclebin to before drop;
    Flashback complete.
    SQL> select table_name 2 from user_tables 3 where table_name = 'TEST_RECYCLEBIN';
    TABLE_NAME ------------------------------ TEST_RECYCLEBIN
    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;
    no rows selected
    SQL>

    This is how we bring a table back from the recycle bin.

    /* Back to sysdba session */
    SQL> select d.tablespace_name , 2 round(d.bytes/1024/1024) total_mb , 3 round(d.bytes/1024/1024)- 4 round(sum(f.bytes)/1024/1024) used_mb, 5 round(sum(f.bytes)/1024/1024) free_mb 6 from dba_data_files d, dba_free_space f 7 where f.file_id = d.file_id 8 and d.tablespace_name = 'TS_RECYCLEBIN' 9 group by d.tablespace_name , d.bytes;
    TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB ------------------------------ ---------- ---------- ---------- TS_RECYCLEBIN 10 5 5
    SQL>

    Here you can see the table is back and the tablespace shows 5Mb used.

    The question that comes to mind next is okay? the space is considered free when the table is in recyclebin, what happens if a new object comes in and need the space? The space being free should be used for the new object, and if that occurs what happens to the table in the recyclebin?

    /* Back to session test_recyclebin */
    SQL> drop table test_recyclebin; Table dropped.
    SQL> select table_name 2 from user_tables 3 where table_name = 'TEST_RECYCLEBIN';
    no rows selected
    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME ------------------------------ ---------------- ------ ------------- BIN$hBJrmLxi49DgQAB/AQAmMA==$0 TEST_RECYCLEBIN TABLE TS_RECYCLEBIN /* Drop a table and verify that it is in the recyclebin. */
    SQL> create table test2_recyclebin 2 as 3 select * from all_objects 4 UNION ALL 5 select * from all_objects;
    Table created.
    SQL> analyze table test2_recyclebin compute statistics;
    Table analyzed.
    SQL> select table_name , round(blocks*8192/1024/1024) size_mb 2 from user_tables 3 where table_name = 'TEST2_RECYCLEBIN';

    TABLE_NAME SIZE_MB ------------------------------ ---------- TEST2_RECYCLEBIN 9 /* We created another table with around 80,000 rows and about 9mb in size. So now we have a 10m auto extend on tablespace with a 5mb hidden object already lying in it. What happens when another 9 Mb object comes in. Even this tablespace is extend able, the space being used by objects in the recyclebin is considered as a free space, so if the new object can fit into the space where recyclebin objects are resting they have to go and make a room for the new objects instead of extending the tablespace. That is called automatic purging of the recyclebin. */

    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin; no rows selected /* Please note the object in the recycle bin is purged (permanently dropped). So whenever you have objects in recyclebin, then keep watching the space utilization of the new objects in the tablespace. */

    SQL> drop table test2_recyclebin; Table dropped.
    SQL> select table_name 2 from user_tables 3 where table_name = 'TEST2_RECYCLEBIN'; no rows selected SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME ------------------------------ ---------------- ------ ------------- BIN$hBJrmLxj49DgQAB/AQAmMA==$0 TEST2_RECYCLEBIN TABLE TS_RECYCLEBIN /* Like I said before a dropped object is just renamed with a system generated unique name and can be seen in the recyclebin. We can no more access it with its original name but we can sure use its new name which is the OBJECT_NAME in the user_recyclebin view. */

    SQL> select count(*) from test2_recyclebin;
    select count(*) from test2_recyclebin * ERROR at line 1: ORA-00942: table or view does not exist
    SQL>select count(*) from "BIN$hBJrmLxj49DgQAB/AQAmMA==$0";
    COUNT(*) ---------- 81378 /* This is how we manually purge objects from recyclebin. */
    SQL> purge table test2_recyclebin; Table purged.
    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;
    no rows selected
    SQL> select count(*) from "BIN$hBJrmLxj49DgQAB/AQAmMA==$0";
    select count(*) from "BIN$hBJrmLxj49DgQAB/AQAmMA==$0" * ERROR at line 1: ORA-00942: table or view does not exist
    SQL> select count(*) from test2_recyclebin;
    select count(*) from test2_recyclebin * ERROR at line 1: ORA-00942: table or view does not exist
    SQL>

    A table once purged from recyclebin is deleted permanently. There is no record of that table in *_tables and *_recyclebin.

    Scenerios where you may have multiple objects with same name in and out of recyclebin:

    /* Back to session test_recyclebin */
    SQL> create table samename (first_in number); Table created.
    SQL> drop table samename; Table dropped.
    SQL> create table samename (last_in number); Table created.
    SQL> drop table samename; Table dropped.
    SQL> select object_name , ORIGINAL_NAME , TYPE , TS_NAME 2 from user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME TYPE TS_NAME ------------------------------ ---------------- ------ ------------- BIN$hBJrmLxk49DgQAB/AQAmMA==$0 SAMENAME TABLE TS_RECYCLEBIN BIN$hBJrmLxl49DgQAB/AQAmMA==$0 SAMENAME TABLE TS_RECYCLEBIN

    SQL> flashback table samename to before drop; Flashback complete.
    SQL> desc samename Name Null?
    Type --------------------- -------- ------------------------ LAST_IN NUMBER
    SQL>

    If you have tables with the same original name in the recyclebin and you issue a FLASHBACK TABLE command using the original name, which is not unique in the recyclebin. Which table will be restored? This works in a LIFO (Last In First Out) fashion, i.e. the table that came in the recyclebin last will be restored.

    SQL> drop table samename;
    Table dropped. /* This is the table that we just restored with a column LAST_IN in it. I dropped it again. */
    SQL> select object_name , ORIGINAL_NAME , DROPTIME 2 from user_recyclebin;

    OBJECT_NAME ORIGINAL_NAME DROPTIME ------------------------------ ---------------- ------------------- BIN$hBJrmLxm49DgQAB/AQAmMA==$0 SAMENAME 2010-04-13:03:43:38 BIN$hBJrmLxk49DgQAB/AQAmMA==$0 SAMENAME 2010-04-13:03:40:29 /* Now if a table that didn''t go Last in the recyclebin and is required to be restored then its unique recyclebin name BIN* can be used to restore it. */

    SQL> flashback table "BIN$hBJrmLxk49DgQAB/AQAmMA==$0" 2 to before drop;
    Flashback complete.
    SQL> desc samename Name Null?

    Type ---------------------- -------- ------------------------ FIRST_IN NUMBER /* The table having column LAST_IN was the one which came last in the recyclebin. But the name we used in FLASHBACK command is the unique name for the table which has Column FIRST_IN in it and we got it back, even it was second in the queue. */

    SQL> flashback table samename to before drop;

    flashback table samename to before drop * ERROR at line 1: ORA-38312: original name is used by an existing object /* Okay. Now that we already have restored an object with the name "samename", if we try to restore another one with the same name we will get an object already exists error. We can rename a table while flashing it back from recyclebin to overcome duplicate table names problem. Please see below. */

    SQL> flashback table samename to before drop rename to samename2;
    Flashback complete.
    SQL> desc samename Name Null? Type ----------------------- -------- ------------------------ FIRST_IN NUMBER
    SQL> desc samename2 Name Null? Type ----------------------- -------- ------------------------ LAST_IN NUMBER
    Last edited by devesh_ocp; 04-26-2012 at 08:43 PM.
    Regards_____
    D
    evesh Chaurasia

    Oracle Administrator

+ Reply to Thread

Similar Threads

  1. Partitioning a table from a normal table
    By devesh_ocp in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 04-24-2012, 04:06 PM
  2. Oracle Database 10g Flashback Technology.
    By ajaychandi in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 2
    Last Post: 02-24-2012, 01:43 PM
  3. Oracle 11gR2 Table level compression.
    By ajaychandi in forum Core Database Administration and Monitoring
    Replies: 0
    Last Post: 01-25-2012, 02:53 PM
  4. Oracle 10g Free up Unused Table Space.
    By ajaychandi in forum Database Performance Management,Database Links,Materialized Views
    Replies: 0
    Last Post: 01-25-2012, 02:48 PM
  5. Restrict Users not to see table
    By ajaysingh in forum Core Database Administration and Monitoring
    Replies: 1
    Last Post: 10-19-2011, 12:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics