Dear All ,
I have simulated a scenario .. Just trying to understand ..what is happening ?
I have a table and contains index and constraint (primary key)
Table tab1 was dropped and recovered from recyclebin
but unable to get back original name of constraint for table tab1.
>>
SQL> create table tab1 as select * From scott.emp;
Table created.
SQL> alter table tab1 add constraint t1_emmno_pk1 primary key (empno);
Table altered.
SQL> create index t1_indx_sal on tab1(sal);
Index created.
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE,INDEX_NAME, STATUS from user_constraints where table_name='TAB1';
CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
T1_EMMNO_PK1 P T1_EMMNO_PK1 ENABLED
SQL> select INDEX_NAME, table_name from user_indexes where table_name='TAB1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
T1_INDX_SAL TAB1
T1_EMMNO_PK1 TAB1
>>
SQL> select * from user_recyclebin;
no rows selected
SQL> drop table tab1;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME,TYPE,DROPTIME,CAN_UNDROP from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME CAN
---------------------------------------------------- -------------- ------------------------- ------------------- ---
BIN$dbxUNNeg+vfgUKjAlZ8n/Q==$0 T1_INDX_SAL INDEX 2018-09-13:17:35:25 NO
BIN$dbxUNNeh+vfgUKjAlZ8n/Q==$0 T1_EMMNO_PK1 INDEX 2018-09-13:17:35:26 NO
BIN$dbxUNNei+vfgUKjAlZ8n/Q==$0 TAB1 TABLE 2018-09-13:17:35:26 YES
>>
SQL> flashback table tab1 to before drop;
Flashback complete.
SQL> select INDEX_NAME, table_name from user_indexes where table_name='TAB1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
BIN$dbxUNNeg+vfgUKjAlZ8n/Q==$0 TAB1
BIN$dbxUNNeh+vfgUKjAlZ8n/Q==$0 TAB1
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE,INDEX_NAME, STATUS from user_constraints where table_name='TAB1';
CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
BIN$dbxUNNef+vfgUKjAlZ8n/Q==$0 P BIN$dbxUNNeh+vfgUKjAlZ8n/Q==$0 ENABLED
>>
SQL> alter index "BIN$dbxUNNeg+vfgUKjAlZ8n/Q==$0" rename to T1_INDX_SAL;
Index altered.
>> How do i rename this constraint and associated index ?
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE,INDEX_NAME, STATUS from user_constraints where table_name='TAB1';
CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
BIN$dbxUNNef+vfgUKjAlZ8n/Q==$0 P BIN$dbxUNNeh+vfgUKjAlZ8n/Q==$0 ENABLED
SQL> select INDEX_NAME, table_name from user_indexes where table_name='TAB1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
T1_INDX_SAL TAB1
BIN$dbxUNNeh+vfgUKjAlZ8n/Q==$0 TAB1
Thanks
DB 11201 on LINUX