19 | 10 | 2017
Latest Articles
Popular Articles

Administration

Flashback Table to before drop

User Rating:  / 0
PoorBest 

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

 

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 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. 

 

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 10
SQL> 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            1
CW4ORA_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.