Oracle restores the index/constraint of the deleted table

Oracle restores the index/constraint of the deleted table

I recently visited AskTom and asked a good question:
Q: How can I restore the index and constraints of the deleted table through the Flashback Technology?
Let's take a look at Tom's answer:
A: In fact, while using Flashback to restore the table to be deleted, its indexes and constraints are also restored, but the name is changed to A string of random characters starting with a bin $, because it retains the name in the recycle bin table.
All we need to do is rename the missing index and constraint name.
Original article: The indexes and constraints are actually restored by flashback, but you might not have recognized them.
They retain their recycle bin names, so their names start with BIN $ followed by seemingly random characters.
They come back with the table, but their names are lost. All you need to do is rename them.
For example:
Alter index "BIN $/y02LoFDTm0bx1GIQtwx0A = $0"
Rename to PK_SR_TEST1;
----------------------------------------------------
Let's begin with an example:
1. Create a test table t:
[SQL]

SCOTT @ orcl> create table t
2 (x int,
3 constraint t_pk primary key (x ),
4 constraint check_x check (x> 0)
5 );
 
The table has been created.

2. Record the SCN when indexes and costraints exist:

Note: scott needs to be granted the dbms_flashback package execution permission to Log On As sys:
[SQL]

SYS @ orcl> grant execute on dbms_flashback to scott;

[SQL]

SCOTT @ orcl> column SCN new_val S
SCOTT @ orcl> select dbms_flashback.get_system_change_number SCN from dual;
 
SCN
----------
11394151

3. drop table t and perform the flash back operation:
[SQL]

SCOTT @ orcl> drop table t;
 
The table has been deleted.
 
SCOTT @ orcl> flashback table t to before drop;
 
Flash back complete.

In this case, the query is visible: The restored index changes to BIN $ xxxxx.
[SQL]

SCOTT @ orcl> column index_name new_val I
SCOTT @ orcl> select index_name
2 from user_indexes
3 where table_name = 'T ';
 
INDEX_NAME
------------------------------
BIN $ vk2xLfpbThSjwsdflA1WjQ ==0 0


4. Now grant scott the permission to flash back query views USER_INDEXES and USER_CONSTRAINTS and execute the flash back query to find the index and constraint name before deletion:
[SQL]

SCOTT @ orcl> conn/as sysdba
Connected.
SYS @ orcl> grant flashback on user_indexes to scott;
 
Authorization successful.
 
SYS @ orcl> grant flashback on user_constraints to scott;
 
Authorization successful.
[SQL]

SYS @ orcl> conn scott/tiger
Connected.
SCOTT @ orcl> column index_name new_val OI
SCOTT @ orcl> select index_name
2 from user_indexes as of scn & S
3 where table_name = 'T ';
Original Value 2: from user_indexes as of scn & S
New Value: 2: from user_indexes as of scn 11394151
 
INDEX_NAME
------------------------------
T_PK

5. Change the index name back to the original value:
[SQL]

SCOTT @ orcl> alter index "& I" rename to "& OI ";
Original Value 1: alter index "& I" rename to "& OI"
New Value: 1: alter index "BIN $ vk2xLfpbThSjwsdflA1WjQ = $0" rename to "T_PK"
 
The index has been changed.

6. Modify the constraints in the same way. We will not repeat them here. Attached to the above operations:

-------------------------------------
Present By Dylan.