Lost Constraint Name

Posted: March 17, 2013 in Database General
Tags: ,

In my previous post (Restoring Table From Recyclebin), i have shown that how the constraint name is lost forever when we restore table from recyclebin. I feel it very strange that i can’t get the original constraint name back, so i asked this question in OTN forum and i came to know that there is way to get the name back using flashback queries. All thanks to “Tom Kyte” for this and original post link can be found here Tom Kyte.

I am just trying to replicate his work and trying to see if i am able to get the same result or not. I am using TIMESTAMP_TO_SCN function to get the SCN number as in real situations we usually know the appworx time when tables dropped from database.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
17-MAR-13 05.40.51.553391 PM +01:00

SQL> create table yya_test_New (name varchar2(10) constraint upper_name check (name=upper(name)));

Table created.

SQL> create index yya_idx1 on yya_test_New(name);

Index created.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

no rows selected

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
UPPER_NAME

SQL> insert into yya_test values('yogesh');
insert into yya_test values('yogesh')
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into yya_test_New values('YOGESH');

1 row created.

SQL> commit;

Commit complete.

SQL> drop table YYA_TEST_NEW;

Table dropped.

Now, let’s find out the original constraint name. Now, i have appwrox time of table dropped and let’s find out the SCN.

SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,TS_NAME,DROPTIME,CAN_UNDROP from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE                      TS_NAME                        DROPTIME            CAN
------------------------------ -------------------------------- ------------------------- ------------------------------ ------------------- ---
BIN$2CK5+mwxAELgQwotDFQAQg==$0 YYA_IDX1                         INDEX                     USERS                          2013-03-17:17:54:32 NO
BIN$2CK5+mwyAELgQwotDFQAQg==$0 YYA_TEST_NEW                     TABLE                     USERS                          2013-03-17:17:54:32 YES

SQL> select CONSTRAINT_NAME from user_constraints;

CONSTRAINT_NAME
------------------------------
BIN$2CK5+mwwAELgQwotDFQAQg==$0

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select timestamp_to_scn('17-MAR-13 05:42:00 PM') from dual;

TIMESTAMP_TO_SCN('17-MAR-1305:42:00PM')
---------------------------------------
                                 236338

SQL> select CONSTRAINT_NAME from dba_constraints as of scn 236338 where owner='BACKUP';

CONSTRAINT_NAME
------------------------------
UPPER_NAME

So, we got the name back…..

Advertisements
Comments
  1. orasteps says:

    Well, i have to set the following to get the flashback query work. Following is documentation link saying that it’s required for flashback query to work;

    http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFGJHCJ

    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
    
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s