Administration

Flashback Table to before drop

User Rating:

/ 1

Details: Parent Category: Articles; Created on Wednesday, 17 March 2010 22:52; Last Updated on Friday, 24 August 2012 17:49; Published on Wednesday, 17 March 2010 22:52; Written by Guy Lambregts; Hits: 4984

Flashback Table to before drop

Yesterday Baldrik came in my office apparently in a terrible mood. He told me one of his junior developpers had dropped one of the most important tables of the db. He was not proud to ask me to restore the db to a previous point in time on another platform, hence I could export the table and import the dump in the production db. That' s what he kept in mind from previous similar experiences.

Actually this sql was going on

SQL> drop table an_time; Table dropped.

I was thinking at our barbeque planned to happen in our garden this weekend. I used to invite people who likes an Irish coffee at the end. Having this in mind I asked Baldrik if he was ready to give me a bottle of Whisky if I could solve the problem in less then 5 minutes. Baldrik offered me 5 bottles. This is how I became drunk last night.

SQL> select owner,object_name,original_name,droptime,dropscn from dba_recyclebin; CW4ORA BIN$1HxRz8L3RlO6tDCHKFUZJA==$0 PK_AN_TIME_KEY_IDX 2005-07-29:13:42:05 6066023 CW4ORA BIN$qrCltrNgS62hbfZjvWPeQQ==$0 AN_TIME 2005-07-29:13:42:05 6066024 TESTORA10 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TEST_FLASH 2005-05-02:17:14:32 4251948 TESTORA10 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 MY_MERGE 2005-05-03:11:25:03 4254806 SQL> flashback table cw4ora.an_time to before drop;

Let' s take a look if the object is still in the recyclebin ? Apparently the table is not anymore in the recyclebin.

SQL> select owner,object_name,original_name,droptime,dropscn from dba_recyclebin;

TESTORA10 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TEST_FLASH 2005-05-02:17:14:32 4251948 TESTORA10 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 MY_MERGE 2005-05-03:11:25:03 4254806

If we take use a select or a describe command we notice the table and the table data has been restored

Let' s take a look if I can select from the object.

Apparently the table has been restored. What about the indexes of the previously dropped table ?

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

BIN$1HxRz8L3RlO6tDCHKFUZJA==$0 VALID

When we flashback a table to before drop then the associated indexes, triggers and constraints are flashbacked as well however they keep their recyclebin name and not their original name

Let' s look in detail

SQL> connect testora10/testora10

Connected.

The command "show recyclebin" shows the users dropped tables and associated objects, here we see 2 dropped tables. ( this command show only the segments which are candidate for a flashback to before drop )

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT_TYPE DROP TIME

MY_MERGE BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 TABLE 2005-05-03:11:25:03 TEST_FLASH BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TABLE 2005-05-02:17:14:32

Let' s take a look in the view user_segments, we still find back the dropped tables, apparently all the segments are 256Kb each ( uniform sized local extent allocation ) . In fact the segments are still there, that' s one of the reason why select statements are still possible.

SQL> select segment_name,segment_type,bytes/(1024*1024) from user_segments where segment_name like 'BIN%';
 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0
 TABLE ,25
 
 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0
 TABLE ,25
 
 BIN$Mpf8c/+mS5C1PuBthHB9Pg==$0
 INDEX ,25
 
 BIN$FQb4988DQIG5dPydO/lyBg==$0
 INDEX ,25
 
 BIN$9HbIlru2T0qgP4fdKWMiWw==$0
 INDEX ,25
 
 BIN$rSD2xsdNT2uTUTiBYCY11g==$0
 INDEX ,25

Let' s take a look in the view user_recyclebin, we find the same space allocation

SQL> select object_name,original_name,droptime,dropscn,(space*8192)/(1024*1024) from user_recyclebin;
 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0
 TEST_FLASH
 2005-05-02:17:14:32 4251948
 ,25
 
 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0
 MY_MERGE
 2005-05-03:11:25:03 4254806
 ,25

Let' s undrop the tables, notice how I overwrite the original name of the segment with the one I prefer.

Flashback Table to before drop

Actually this sql was going on

SQL> drop table an_time; Table dropped.

Let' s take a look if the object is still in the recyclebin ? Apparently the table is not anymore in the recyclebin.

SQL> select owner,object_name,original_name,droptime,dropscn from dba_recyclebin; TESTORA10 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TEST_FLASH 2005-05-02:17:14:32 4251948 TESTORA10 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 MY_MERGE 2005-05-03:11:25:03 4254806

If we use a select or a describe command we notice the table and the table data has been restored

Let' s take a look if I can select from the object.

Apparently the table has been restored. What about the indexes of the previously dropped table ?

SQL> select index_name,status from dba_indexes where table_name='AN_TIME';
BIN$1HxRz8L3RlO6tDCHKFUZJA==$0 VALID

When we flashback a table to before drop then the associated indexes, triggers and constraints are flashbacked as well however they keep their recyclebin name and not their original name

Let' s look in detail

SQL> connect testora10/testora10
Connected.

SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

MY_MERGE BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 TABLE 2005-05-03:11:25:03 TEST_FLASH BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TABLE 2005-05-02:17:14:32

SQL> select segment_name,segment_type,bytes/(1024*1024) from user_segments where segment_name like 'BIN%';
BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 TABLE ,25 BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TABLE ,25 BIN$Mpf8c/+mS5C1PuBthHB9Pg==$0 INDEX ,25 BIN$FQb4988DQIG5dPydO/lyBg==$0 INDEX ,25 BIN$9HbIlru2T0qgP4fdKWMiWw==$0 INDEX ,25 BIN$rSD2xsdNT2uTUTiBYCY11g==$0 INDEX ,25

Let' s take a look in the view user_recyclebin, we find the same space allocation

SQL> select object_name,original_name,droptime,dropscn,(space*8192)/(1024*1024) from user_recyclebin;
BIN$wAxhqXZDS5WAVQEebDdsCQ==$0 TEST_FLASH 2005-05-02:17:14:32 4251948 ,25 BIN$WjgYLjCSRre4OQ3NfMbXRQ==$0 MY_MERGE 2005-05-03:11:25:03 4254806 ,25

Let' s undrop the tables, notice how I overwrite the original name of the segment with the one I prefer.

SQL> flashback table my_merge to before drop;Flashback complete. SQL> flashback table test_flash to before drop rename to my_flashbacked_table; Flashback complete. SQL> select object_name,original_name,droptime,dropscn,(space*8192)/(1024*1024) from user_recyclebin; no rows selected

I still have these "like BIN%" index segments

SQL> select segment_name,segment_type,bytes/(1024*1024) from user_segments where segment_name like 'BIN%';
BIN$Mpf8c/+mS5C1PuBthHB9Pg==$0 INDEX ,25 BIN$FQb4988DQIG5dPydO/lyBg==$0 INDEX ,25 BIN$9HbIlru2T0qgP4fdKWMiWw==$0 INDEX ,25 BIN$rSD2xsdNT2uTUTiBYCY11g==$0 INDEX ,25

They belong to a table which I dropped and flashback dropped (not demonstrated here), once more this proves : when we flashback a table to before drop then the associated indexes, triggers and constraints are flashbacked as well however they keep their recyclebin name and not their original name. If however we drop an index before the drop of the table, the index drop bypasses the recyclebin, as such is irreversible.

SQL> select table_name from user_indexes where index_name like 'BIN%';
PORDER PORDER PORDER PORDER

Let' s take a look to free space management

SQL> column tablespace_name format a20 SQL> set numwidth 10SQL> select tablespace_name,sum(bytes/(1024*1024)) "FREE BYTES" from dba_free_space group by tablespace_name; TABLESPACE_NAME FREE BYTES

CW4ORA_DATA 1 CW4ORA_INDX ,875 DAMIEN 91,5 EXAMPLE 70,0625 SYSAUX 7,1875 SYSTEM 18,9375 TEST_ORA10 227,75 >> We have 227,75 Mb free in tablespace test_ora10. UNDOTBS1 130,3125 USERS 5,9375

What happens with the free space if we drop a table and its associated indexes ?

SQL> select owner,segment_name,bytes/(1024*1024) from dba_segments where segment_type='TABLE' and tablespace_name='TEST_ORA10' and bytes/(1024*1024) > 10 and rownum < 6;
TESTORA10 TEST_ITL1 24,75 TESTORA10 PORDER3 131 TESTORA10 TEST_ITL3 23,25
SQL> drop table testora10.porder3;
Table dropped. SQL> select tablespace_name,sum(bytes/(1024*1024)) "FREE BYTES" from dba_free_space group by tablespace_name;
CW4ORA_DATA 1CW4ORA_INDX ,875 DAMIEN 91,5 EXAMPLE 70,0625 SYSAUX 7,1875 SYSTEM 18,9375 TEST_ORA10 358,75 >>> We have 358,75 Mb free in the tablespace test_ora10. UNDOTBS1 130,3125 USERS 5,9375

We notice the space of the dropped objects is considered as free space, however the dropped objects are still there, they are even at the same place as the original segment.

SQL> select header_file,header_block from dba_segments where segment_name='PORDER3' and owner='TESTORA10';
9 27689
SQL> select a.header_file,a.header_block from dba_segments a,dba_recyclebin b where a.segment_name=b.object_name and b.original_name='PORDER3';
9 27689

The reason why dropped objects are considered as not occupying space anymore is that in case of space pressure the space of the recyclebin reclaimable is, in such a case dropped tables cannot be flashbacked anymore !

This is the way how we can manually purge the recyclebin.

SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> select original_name from dba_recyclebin; no rows selected

This is the way how we can manually purge the dropped objects (and dependant objects) of a given user in a given tablespace.

SQL> purge tablespace test_ora10 user testora10;

Tablespace purged.

This is the way how we can manually purge a dropped object (and dependant objects)

SQL> purge table testora10.porder;

purge table testora10.porder

ERROR at line 1:
ORA-38307: object not in RECYCLE BIN

This is the way how we can drop an object (and dependant objects) and bypass the recyclebin

SQL> drop table testora10.my_table1 purge;

Table dropped.

You bypass also the recyclebin when you issue statements like drop user cascade, and drop tablespace including contents. Bitmap join indexes, materialized view logs and foreign keys are not recoverable either.