闪回之 回收站、Flashback Drop （table、index、trigger等）

一： Flashback Drop 操作流程

模式一：drop table 后未新建同名表

SQL> create table flashdrop as select * from user_objects;

Table created.

SQL> create bitmap index ind_flashdrop on flashdrop(object_type);

Index created.

SQL> drop table flashdrop;

Table dropped.

--查看 recyclebin 内的对象

SQL> select original_name,object_name,type,droptime from recyclebin;

ORIGINAL_NAME OBJECT_NAME TYPE DROPTIME

-------------------------------- ------------------------------ ------------------------- -------------------

IND_FLASHDROP BIN$ESs42vP2YC3gUw0ZZAqeww==$0 INDEX 2015-03-13:08:08:19

FLASHDROP BIN$ESs42vP3YC3gUw0ZZAqeww==$0 TABLE 2015-03-13:08:08:19

SQL> flashback table flashdrop to before drop;

Flashback complete.

no rows selected

--查看索引名字

SQL> col column_name for a40

SQL> SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name = 'FLASHDROP';

INDEX_NAME COLUMN_NAME DESC

------------------------------ ---------------------------------------- ----

BIN$ESs42vP2YC3gUw0ZZAqeww==$0 OBJECT_TYPE ASC

--索引改为原来的名字 （说明 闪回表 后，即使未给索引重命名，执行计划依然可以走索引）

SQL> alter index "BIN$ESs42vP2YC3gUw0ZZAqeww==$0" rename to IND_FLASHDROP;

Index altered.

--查看是否成功改名

IND_FLASHDROP OBJECT_TYPE ASC

SQL> select count(*) from flashdrop;

COUNT(*)

----------

11

补充：

--查看表约束名

select CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME from user_cons_columns where TABLE_NAME='FLASHDROP';

模式二：drop table 后新建同名表

IND_FLASHDROP BIN$ESs42vP4YC3gUw0ZZAqeww==$0 INDEX 2015-03-13:08:25:37

FLASHDROP BIN$ESs42vP5YC3gUw0ZZAqeww==$0 TABLE 2015-03-13:08:25:37

flashback table flashdrop to before drop

*

ERROR at line 1:

ORA-38312: original name is used by an existing object

SQL> flashback table flashdrop to before drop rename to flashtable;

SQL> select count(*) from flashtable;

模式三：drop table 后新建同名表，再 drop 新同名表

13

SQL> insert into flashdrop(object_name) values('andy');

1 row created.

14

FLASHDROP BIN$ESs42vP9YC3gUw0ZZAqeww==$0 TABLE 2015-03-13:08:42:21

FLASHDROP BIN$ESs42vP+YC3gUw0ZZAqeww==$0 TABLE 2015-03-13:08:56:16

SQL> select count(*) from "BIN$ESs42vP9YC3gUw0ZZAqeww==$0";

SQL> select count(*) from "BIN$ESs42vP+YC3gUw0ZZAqeww==$0";

SQL> flashback table "BIN$ESs42vP+YC3gUw0ZZAqeww==$0" to before drop;

文章可以转载，必须以链接形式标明出处。

本文转自 张冲andy 博客园博客，原文链接：http://www.cnblogs.com/andy6/p/6279989.html   ，如需转载请自行联系原作者