Oracle恢复被删表的索引/约束
Oracle恢复被删表的索引/约束
最近逛AskTom，遇到一个不错的提问：
Q：通过Flashback技术如何恢复被删表的索引及约束？
来看看Tom 大神的回答：
A：其实在使用Flashback恢复被删表的同时，其indexes和constraints也被恢复了，只不过名字变为了一BIN$开头的一串随机字符，因为其保留了再recycle bin表中的名字。
我们要做的就是重新命名丢失的索引和约束名。
原文：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、创建测试表 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 );
表已创建。
2、记录indexes 和 costraints存在时的SCN：
注意：此处需要给scott授予 dbms_flashback包执行权限，以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表 t，并进行闪回操作：
[sql]
SCOTT@orcl> drop table t;
表已删除。
SCOTT@orcl> flashback table t to before drop;
闪回完成。
此时，查询可见：恢复后的索引变为：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
4、现在授予scott用户闪回查询视图USER_INDEXES和USER_CONSTRAINTS权限并执行闪回查询，找到删除前的索引及约束名：
[sql]
SCOTT@orcl> conn / as sysdba
已连接。
SYS@orcl> grant flashback on user_indexes to scott;
授权成功。
SYS@orcl> grant flashback on user_constraints to scott;
授权成功。
[sql]
SYS@orcl> conn scott/tiger
已连接。
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';
原值 2: from user_indexes as of scn &S
新值 2: from user_indexes as of scn 11394151
INDEX_NAME
------------------------------
T_PK
5、将索引名改回原值：
[sql]
SCOTT@orcl> alter index "&I" rename to "&OI";
原值 1: alter index "&I" rename to "&OI"
新值 1: alter index "BIN$vk2xLfpbThSjwsdflA1WjQ==$0" rename to "T_PK"
索引已更改。
6、修改约束方法一致，此处不再赘述。附上以上操作相关效果图：
-------------------------------------
Present By Dylan.
最近逛AskTom，遇到一个不错的提问：
Q：通过Flashback技术如何恢复被删表的索引及约束？
来看看Tom 大神的回答：
A：其实在使用Flashback恢复被删表的同时，其indexes和constraints也被恢复了，只不过名字变为了一BIN$开头的一串随机字符，因为其保留了再recycle bin表中的名字。
我们要做的就是重新命名丢失的索引和约束名。
原文：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、创建测试表 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 );
表已创建。
2、记录indexes 和 costraints存在时的SCN：
注意：此处需要给scott授予 dbms_flashback包执行权限，以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表 t，并进行闪回操作：
[sql]
SCOTT@orcl> drop table t;
表已删除。
SCOTT@orcl> flashback table t to before drop;
闪回完成。
此时，查询可见：恢复后的索引变为：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
4、现在授予scott用户闪回查询视图USER_INDEXES和USER_CONSTRAINTS权限并执行闪回查询，找到删除前的索引及约束名：
[sql]
SCOTT@orcl> conn / as sysdba
已连接。
SYS@orcl> grant flashback on user_indexes to scott;
授权成功。
SYS@orcl> grant flashback on user_constraints to scott;
授权成功。
[sql]
SYS@orcl> conn scott/tiger
已连接。
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';
原值 2: from user_indexes as of scn &S
新值 2: from user_indexes as of scn 11394151
INDEX_NAME
------------------------------
T_PK
5、将索引名改回原值：
[sql]
SCOTT@orcl> alter index "&I" rename to "&OI";
原值 1: alter index "&I" rename to "&OI"
新值 1: alter index "BIN$vk2xLfpbThSjwsdflA1WjQ==$0" rename to "T_PK"
索引已更改。
6、修改约束方法一致，此处不再赘述。附上以上操作相关效果图：
-------------------------------------
Present By Dylan.
