Skip to Main Content
  • Questions
  • restoring index/constraints of a dropped table!

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, OnkarNath.

Asked: November 06, 2012 - 12:13 am UTC

Last updated: March 25, 2013 - 10:01 am UTC

Version: 11.1.0

Viewed 10K+ times! This question is

You Asked

Tom,

I have a table with a Primary key constraint and associated index:
SQL:SCOTT@tp11g>create table test1(sr number,name char(10));

Table created.

SQL:SCOTT@tp11g>alter table test1 add constraint pk_sr_test1 primary key(sr);

Table altered.

SQL:SCOTT@tp11g>select index_name from user_indexes where table_name='TEST1';

INDEX_NAME
------------------------------
PK_SR_TEST1

SQL:SCOTT@tp11g>drop table test1;

Table dropped.

SQL:SCOTT@tp11g>flashback table test1 to before drop;

Flashback complete.

SQL:SCOTT@tp11g>select index_name from user_indexes where table_name='TEST1';

INDEX_NAME
------------------------------
BIN$/yO2LoFDTmObx1GIQtwxOA==$0

SQL:SCOTT@tp11g>show recyclebin
SQL:SCOTT@tp11g>select constraint_name from user_constraints where table_name='TEST1';

CONSTRAINT_NAME
------------------------------
BIN$JqzNj5O/TLi7/Zt2WDNSfQ==$0

SQL:SCOTT@tp11g>


My question is: even though using FLASHBACK we can restore a table but how do I restore the constraints and indexes associated with this table? One option what I have is I can recreate the indexes and constraints but is it possible to restore indexes and/or constraints using FLASHBACK command?

Thanks
-Onkar

and Tom said...

the indexes and constraints are automagically restored.

you just need to rename them.

alter index "BIN$/yO2LoFDTmObx1GIQtwxOA==$0" rename to PK_SR_TEST1;

for example

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t ( x int, constraint t_pk primary key(x), constraint check_x check(x>0) );

Table created.

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> flashback table t to before drop;

Flashback complete.

ops$tkyte%ORA11GR2> column index_name new_val I
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
BIN$zdTHt0feVm7gQwEAAH9pjg==$0

ops$tkyte%ORA11GR2> alter index "&I" rename to t_pk;
old   1: alter index "&I" rename to t_pk
new   1: alter index "BIN$zdTHt0feVm7gQwEAAH9pjg==$0" rename to t_pk

Index altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column constraint_name new_val C
ops$tkyte%ORA11GR2> select constraint_name from user_constraints where table_name = 'T' and constraint_type = 'C';

CONSTRAINT_NAME
------------------------------
BIN$zdTHt0fcVm7gQwEAAH9pjg==$0

ops$tkyte%ORA11GR2> alter table t rename constraint "&C" to check_x;
old   1: alter table t rename constraint "&C" to check_x
new   1: alter table t rename constraint "BIN$zdTHt0fcVm7gQwEAAH9pjg==$0" to check_x

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
T_PK

ops$tkyte%ORA11GR2> select constraint_name from user_constraints where table_name = 'T' and constraint_type = 'C';

CONSTRAINT_NAME
------------------------------
CHECK_X

ops$tkyte%ORA11GR2> 

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

how to find the original name of the dropped constraint ?

Sokrates, November 06, 2012 - 9:10 am UTC

is there any way how we can find the original name of the dropped constraint ?

sokrates@11.2 > create table t ( x int, constraint t_pk primary key(x), constraint check_x check(x>0) );

Table created.

sokrates@11.2 > drop table t;

Table dropped.

sokrates@11.2 > select object_name, type, original_name from user_recyclebin;

OBJECT_NAME                    TYPE                      ORIGINAL_NAME
------------------------------ ------------------------- --------------------------------
BIN$zdXaOw2fWG/gQBwK3yg4Rw==$0 INDEX                     T_PK
BIN$zdXaOw2gWG/gQBwK3yg4Rw==$0 TABLE                     T


I can see the original name of the dropped table and the original name of the dropped index, but is there some dictionary view where I can find the name of the dropped checked constraint check_x ?
It was renamed to
sokrates@11.2 > select constraint_name from user_constraints where constraint_type='C' and table_name=(select object_name from user_recyclebin where original_name='T' and type='TABLE');

CONSTRAINT_NAME
------------------------------
BIN$zdXaOw2dWG/gQBwK3yg4Rw==$0

by the drop command
Tom Kyte
November 06, 2012 - 10:30 am UTC

we do not track that, however - you can use flashback query on the dictionary perhaps to retrieve this, if your undo retention is good enough. You can use the droptime from user_recyclebin to query "as of" instead of the way I'm doing it below with dbms_flashback.get_system_change_number

ops$tkyte%ORA11GR2> create table t ( x int, constraint t_pk primary key(x), constraint check_x check(x>0) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> column SCN new_val S
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number SCN from dual;

       SCN
----------
 105021336

ops$tkyte%ORA11GR2> pause

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> flashback table t to before drop;

Flashback complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column index_name new_val I
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
BIN$zdcXg9tLXdPgQwEAAH/vtw==$0

ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> column index_name new_val OI
sys%ORA11GR2> select index_name from dba_indexes as of scn &S where owner = 'OPS$TKYTE' and table_name = 'T';
old   1: select index_name from dba_indexes as of scn &S where owner = 'OPS$TKYTE' and table_name = 'T'
new   1: select index_name from dba_indexes as of scn  105021336 where owner = 'OPS$TKYTE' and table_name = 'T'

INDEX_NAME
------------------------------
T_PK

sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter index "&I" rename to "&OI";
old   1: alter index "&I" rename to "&OI"
new   1: alter index "BIN$zdcXg9tLXdPgQwEAAH/vtw==$0" rename to "T_PK"

Index altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column constraint_name new_val C
ops$tkyte%ORA11GR2> select constraint_name from user_constraints where table_name = 'T' and constraint_type = 'C';

CONSTRAINT_NAME
------------------------------
BIN$zdcXg9tJXdPgQwEAAH/vtw==$0

ops$tkyte%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> column constraint_name new_val OC
sys%ORA11GR2> select constraint_name from dba_constraints as of scn &S where owner = 'OPS$TKYTE' and table_name = 'T' and constraint_type = 'C';
old   1: select constraint_name from dba_constraints as of scn &S where owner = 'OPS$TKYTE' and table_name = 'T' and constraint_type = 'C'
new   1: select constraint_name from dba_constraints as of scn  105021336 where owner = 'OPS$TKYTE' and table_name = 'T' and constraint_type = 'C'

CONSTRAINT_NAME
------------------------------
CHECK_X

sys%ORA11GR2> connect /
Connected.
ops$tkyte%ORA11GR2> alter table t rename constraint "&C" to "&OC";
old   1: alter table t rename constraint "&C" to "&OC"
new   1: alter table t rename constraint "BIN$zdcXg9tJXdPgQwEAAH/vtw==$0" to "CHECK_X"

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
T_PK

ops$tkyte%ORA11GR2> select constraint_name from user_constraints where table_name = 'T' and constraint_type = 'C';

CONSTRAINT_NAME
------------------------------
CHECK_X

OnkarNath Tiwary, November 06, 2012 - 10:46 pm UTC

Thanks Tom.

Foreign Keys are not flashed back

Mohamed, March 15, 2013 - 11:05 am UTC

Hi Tom,

Just to point out that Foreign keys are not flashed back

http://hourim.wordpress.com/2012/11/14/recycle-bin-whats-going-on/

Best regards
Tom Kyte
March 25, 2013 - 10:01 am UTC

related DDL are not - true, this is correct.


if you drop a parent table - you did it with "cascade constraints". The cascade constraint is separate from the dropping of the table - the constraints are dropped themselves - and constraints by themselves to not go into the recycle bin.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.