Oracle Database Discussions

DB Version : 10.2.0.4
OS Version : AIX 6.1

Recently there is situation where i have to recert objects from recyclebin. Tables, indexes & triggers got reverted without any issue as their original names are
displayed in user_recyclebin. But problem come with constraints. Below is link to my blog where i have put this in detail.

http://orasteps.wordpress.com/2013/03/14/restoring-table-from-recyclebin/

I am putting here a small example which i put on my blog too, just to show that constraint name is lost.
One can argue that most of time constraint name are system generated only and there is no use in reverting to original name, what it makes a difference if a
constraint is maintained with name "SYS_C222222%" or "BIN$==00%".

Is there any reason for not capturing the constraints name in recyclebin ? Please comment.....

SQL> create table yya_test (name varchar2(10) constraint upper_name check (name=upper(name)));
 
Table created.
 
SQL> create index yya_idx on yya_test(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-02290: check constraint (BACKUP.UPPER_NAME) violated
 
SQL> insert into yya_test values('YOGESH');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 

Now, as soon as i drop the table, i got entries for table & index into recyclebin and constraint got renamed and original name is lost for constraint. I would love to raise this point to oracle (if i know where to raise), that constraint name should not be lost. Here are the line that shows that constraint name is lost:

SQL> drop table yya_test;
 
Table dropped.
 
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$1+WNXaW1AGTgQwotC20AZA==$0 YYA_IDX INDEX USERS 2013-03-14:17:34:02 NO
 BIN$1+WNXaW2AGTgQwotC20AZA==$0 YYA_TEST TABLE USERS 2013-03-14:17:34:02 YES
 
SQL> select CONSTRAINT_NAME from user_constraints;
 
CONSTRAINT_NAME
 ------------------------------
 BIN$1+WNXaW0AGTgQwotC20AZA==$0

Thanks


Yogesh
http://orasteps.wordpress.com/