利用flashback drop来闪回被误删除的表
1 flashback drop介绍
flashback drop是从oracle10g开始出现的，用于恢复用户误删除的对象，这个技术依赖于tablespace recycle bin，这个功能类似windows的回收站。
flashback drop不支持sys用户及使用system表空间的用户。因为show recyclebin 为空。
从oracle10g开始，每个表空间都会有一个叫做回收站的逻辑区域，当用户执行drop命令时，被删除的表和表的关联对象（包括索引、约束、触发器、LOB段、LOB index段）不会被物理删除，这些对象被转移到回收站中，这就给用户提供了一个恢复的可能。
2 recyclebin参数
初始化参数recyclebin用于控制是否启用recyclebin功能，默认是on，可以使用off关闭。如下：
（1）10g打开关闭回收站
alter system set recyclebin=off;
alter system set recyclebin=on;
11g变化：
version parameter name data type session modifieble system modifieble
10.2.0 recyclebin string true immediate
11.1.0 recyclebin string true deferred
SQL> select name,isses_modifiable,issys_modifiable from v$parameter where name='recyclebin';
NAME ISSES_MODIFIABL ISSYS_MODIFIABLE
---------------------------------------- --------------- ---------------------------
recyclebin TRUE DEFERRED
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string on
（2）11g打开关闭回收站
Oracle参数信息可以通过视图v$parameter查看，其中有一列issys_modifiable表示参数类型，有immediate,false,deferred三种，其中false表示是静态参数，只要当instance重新启动时才会生效，其他两种是动态参数。immediate表示修改参数后立即生效，deferred表示修改后要等下一个session才能生效，修改时已经连接的session是不会生效的。在用alter修改参数时如果是false或immediate一般可以不写，如果是deferred则要写。
----基于会话关闭
SQL> conn shall/shall
Connected.
SQL> create table test(id int);
Table created.
SQL> drop table test;
Table dropped.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
no rows selected
SQL> alter session set recyclebin=off;
Session altered.
SQL> drop table test;
Table dropped.
SQL> flashback table test to before drop;
flashback table test to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
----加参数deferred关闭
SQL> conn / as sysdba
Connected.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string on
SQL> alter system set recyclebin=off deferred;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 12:42:44 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string OFF
----启用
SQL> alter system set recyclebin=on deferred;
System altered.
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string OFF
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 15 12:46:27 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter recyclebin
SQL> set linesize 200
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string ON
SQL>
3 误drop表后恢复
（1）确定回收站启用
----与开归档和开flashback与否无关
sys@ORCL>select log_mode,flashback_on from v$database;
LOG_MODE FLASHBACK_ON
------------ ------------------
NOARCHIVELOG NO
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string ON
（2）实验环境准备
SQL> conn shall/shall
Connected.
SQL> create table zhong1(id int);
Table created.
SQL> create table zhong2(id int);
Table created.
SQL> drop table zhong1;
Table dropped.
SQL> drop table zhong2 purge; ----删除加参数purge，表直接删除，不会写入到recyclebin中
Table dropped.
（3）恢复drop表
----查看recylebin中的对象
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
ZHONG1 BIN$Mttj9AT00RLgUKjAC2RX0g==$0 TABLE 2016-05-15:13:00:44
SQL> select object_name,original_name from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------
BIN$Mttj9AT00RLgUKjAC2RX0g==$0 ZHONG1
----查看对象的内容
SQL> select * from "BIN$Mttj9AT00RLgUKjAC2RX0g==$0";
no rows selected
SQL> desc "BIN$Mttj9AT00RLgUKjAC2RX0g==$0"
Name Null? Type
----------------------------------------------------------------------------------------------------------------- --------
ID NUMBER(38)
----恢复drop表
SQL> flashback table zhong1 to before drop;
Flashback complete.
SQL> flashback table zhong2 to before drop;
flashback table zhong2 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
SQL> desc zhong1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- --------
ID NUMBER(38)
4 recycle bin管理
表空间的recyclebin 区域只是一个逻辑区域，而不是从表空间上物理的划出一块区域固定用于回收站，因此recyclebin是和普通对象共用表空间的存储区域，或者说是recyclebin的对象要和普通对象抢夺存储空间。当发生空间不够时，oracle会按照先入先出的顺序覆盖recyclebin中的对象。也可以手动的删除recyclebin占用的空间。
---手动清理recyclebin
1）purge tablespace tablespace_name; ----清空该表空间回收站
2）purge tablespace tablespace_name user user_name; ----清空该表空间回收站中指定用户的对象
3）purge recyclebin; ----清空当前用户的回收站
4）purge dba_recyclebin; ----清空所有用户的回收站
5）drop table table_name pure; ----删除表时不放回收站，永久删除该表
6）purge index recyle_bin_object_name; ----释放回收站中该对象的索引所占用的空间（释放后，表可以恢复，索引需要重建）
----drop恢复演练
sys@ORCL>conn shall/shall
Connected.
shall@ORCL>purge recyclebin;
Recyclebin purged.
shall@ORCL>show recyclebin
shall@ORCL>create table zhong(id number);
Table created.
shall@ORCL>insert into zhong values(111);
1 row created.
shall@ORCL>commit;
Commit complete.
shall@ORCL>drop table zhong;
Table dropped.
shall@ORCL>create table zhong(id number);
Table created.
shall@ORCL>insert into zhong values(111);
1 row created.
shall@ORCL>insert into zhong values(111);
1 row created.
shall@ORCL>commit;
Commit complete.
shall@ORCL>drop table zhong;
Table dropped.
shall@ORCL>create table zhong(id number);
Table created.
shall@ORCL>drop table zhong;
Table dropped.
shall@ORCL>create table zhong(id number);
Table created.
shall@ORCL>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
ZHONG BIN$McDeSLkfN9fgU28LqMBn5A==$0 TABLE 2016-05-01:11:59:37
ZHONG BIN$McDeSLkeN9fgU28LqMBn5A==$0 TABLE 2016-05-01:11:57:33
ZHONG BIN$McDeSLkdN9fgU28LqMBn5A==$0 TABLE 2016-05-01:11:57:01
----使用原有的表名称来清空回收站中的表，默认清空回收站中最早的同名表
shall@ORCL>purge table zhong;
Table purged.
shall@ORCL>show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
ZHONG BIN$McDeSLkfN9fgU28LqMBn5A==$0 TABLE 2016-05-01:11:59:37
ZHONG BIN$McDeSLkeN9fgU28LqMBn5A==$0 TABLE 2016-05-01:11:57:33
----闪回恢复drop表
shall@ORCL>flashback table zhong to before drop;
flashback table zhong to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
因为数据库中该用户下已经有该表，因此需要重命名，如下：
shall@ORCL>flashback table zhong to before drop rename to zhongdrop;
Flashback complete.
----使用原有的表名称来闪回回收站中的表，默认闪回回收站中最晚的同名表
----也可以使用回收站中的‘recyclebin name’指定需要删除或闪回的表
shall@ORCL>flashback table "BIN$McDeSLkeN9fgU28LqMBn5A==$0" to before drop rename to zhongdrop2;
Flashback complete.
shall@ORCL>select * from zhongdrop2;
ID
----------
111
111
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30130773/viewspace-2102636/ ，如需转载，请注明出处，否则将追究法律责任。
