MyException - 我的异常网
当前位置:我的异常网» 数据库 » Oracle回复被删表的索引/约束

Oracle回复被删表的索引/约束

www.myexceptions.net  网友分享于:2013-04-10  浏览:15次
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:

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登录:

SYS@orcl> grant execute on dbms_flashback to scott;

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

       SCN
----------
  11394151

3、drop表 t,并进行闪回操作:

SCOTT@orcl> drop table t;

表已删除。

SCOTT@orcl> flashback table t to before drop;

闪回完成。

此时,查询可见:恢复后的索引变为:BIN$xxxxx.

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权限并执行闪回查询,找到删除前的索引及约束名:

SCOTT@orcl> conn / as sysdba
已连接。
SYS@orcl> grant flashback on user_indexes to scott;

授权成功。

SYS@orcl> grant flashback on user_constraints to scott;

授权成功。
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、将索引名改回原值:

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.








文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有