闪回（关于回收站）

--整理以前的学习笔记


1、从回收站recyclebin闪回被删除的表
conn hr/hr
purge recyclebin;
SQL> create table t (id number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_t_id on t(id);

Index created.

SQL> col object_name for a20
SQL> select object_name,object_type from user_objects where created > sysdate-0.1;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
T                    TABLE
IDX_T_ID             INDEX

SQL> drop table t;--此时user_objects中没有数据，recyclebin中有数据了

Table dropped.

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

OBJECT_NAME                    ORIGINAL_NAME                    TYPE       CAN
------------------------------ -------------------------------- ---------- ---
BIN$zsHhz8usBbXgQKjACgEYgg==$0 IDX_T_ID                         INDEX      NO
BIN$zsHhz8utBbXgQKjACgEYgg==$0 T                                TABLE      YES

下面从回收站中恢复

SQL> flashback table t to before drop;

Flashback complete.

此时查询回收站已经为空，查询user_objects发现数据已经回来，表的名称还是原来的名称，索引的名称是回收站里的名称
SQL> select object_name,object_type from user_objects where created > sysdate-0.1;

OBJECT_NAME                                  OBJECT_TYPE
-------------------------------------       -------------------
BIN$zsHhz8usBbXgQKjACgEYgg==$0 INDEX
T                                                        TABLE

事实上只有表的名称会保留原来的名称，而表的从属对象如索引、触发器、约束都保留回收站里的名称，如果需要索引、

触发器、约束等也保留原来的名称，需要查询回收站，并在还原表后重新命名其他对象。

alter index "BIN$zsHhz8usBbXgQKjACgEYgg==$0" rename to IDX_T_ID;
alter table t rename constraint "..." to ...;

如果尝试还原删除后已经重新创建的表，除非使用rename to子句将要还原的表重命名成其他名字，否则将收到一条错误

信息，如下：
SQL> drop table t;

Table dropped.

SQL> create table t(id number);

Table created.

SQL> flashback table t to before drop;
flashback table t to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

SQL> flashback table t to before drop rename to old_t;

Flashback complete.

SQL> select * from old_t;

        ID
----------
         1

2、关于recyclebin

SQL> create table abc(id int,name varchar2(10));
 
Table created
SQL> insert into abc values(1,'aa');
 
1 row inserted
SQL> commit;
 
Commit complete
 
SQL> select * from abc;
 
                                     ID NAME
--------------------------------------- ----------
                                      1 aa
 
SQL> drop table abc;
 
Table dropped

SQL> create table abc(id int,name varchar2(10));
 
Table created
 
SQL> insert into abc values(2,'aa');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from abc;
 
                                     ID NAME
--------------------------------------- ----------
                                      2 aa
 
SQL> drop table abc;
 
Table dropped

SQL> create table abc(id int,name varchar2(10));
 
Table created
 
SQL> insert into abc values(3,'aa');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> drop table abc;
 
Table dropped

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ABC              BIN$SRIAjHRRQLWuVx+S3tk2rg==$0 TABLE        2013-08-12:18:25:00
ABC              BIN$sBtTEK0cRAqRKzSjkSu1Qg==$0 TABLE        2013-08-12:18:23:40
ABC              BIN$AN0mu8qXSZCQjrzySMql+w==$0 TABLE        2013-08-12:18:19:21

SQL> purge table abc;

表已清除。

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ABC              BIN$SRIAjHRRQLWuVx+S3tk2rg==$0 TABLE        2013-08-12:18:25:00
ABC              BIN$sBtTEK0cRAqRKzSjkSu1Qg==$0 TABLE        2013-08-12:18:23:40
一次清除一个，会把回收站中最老的一个版本清除掉

SQL> flashback table abc to before drop;

闪回完成。

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
ABC              BIN$sBtTEK0cRAqRKzSjkSu1Qg==$0 TABLE        2013-08-12:18:23:40

SQL> select * from abc;

        ID NAME
---------- ----------
         3 aa

可以知道，闪回的是最新的一个版本

注意：
1)system表空间中的对象不进recyclebin
2)每个用户都有各自的recyclebin，删除用户之后，该用户回收站中的对象也都清空

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26524307/viewspace-1061352/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26524307/viewspace-1061352/