闪回表操作
先了解一下Recycle Bin:(回收站)
顾名思义RecycleBin在oracle中起到了回收站的作业,当用户删除一个表时,表面上该表已经被删除,实质上oracle会将该表重命名后连同她的相关对象一起放入Recycle Bin中。
SQL> create table emp1 as select * from emp;
Table created.
SQL> drop table emp1;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1 BIN$9Ox0/5P3HKjgQAB/AQAQGg==$0 TABLE 2014-03-19:10:19:18
ORIGINAL NAME:被删除的表名
RECYCLEBIN NAME:被删除后Oracle重命名的新表名
RECYCLEBIN或USER_RECYCLEBIN视图可以查看更加详细的删除信息
SQL> desc recyclebin;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
清除Recycle Bin中的数据:
SQL> purge table emp1;
Table purged.
SQL> show recyclebin;
闪回表测试:
简单的删除表:
SQL> create table emp1 as select * from emp;
Table created.
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP1
删除表
SQL> drop table emp1;
Table dropped.
查看Recycle Bin中的记录
SQL> select OBJECT_NAME,ORIGINAL_NAME from recyclebin;
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------- -----------------
BIN$9Ox0/5P5HKjgQAB/AQAQGg==$0 EMP1
闪回表:
SQL> flashback table emp1 to before drop;
Flashback complete.
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
EMP1
SQL> select OBJECT_NAME,ORIGINAL_NAME from recyclebin;
no rows selected
注意:表虽然得到了恢复,但是表上的索引未必恢复,需要手动的去找到相应的对象进行恢复,或重建索引。
表的删除期间,有同名表也被删除的恢复:
SQL> drop table emp1;
Table dropped.
SQL> create table emp1 as select * from emp;
Table created.
SQL> drop table emp1;
Table dropped.
SQL> create table emp1 as select * from emp;
Table created.
SQL> drop table emp1;
Table dropped.
SQL> select OBJECT_NAME,ORIGINAL_NAME,DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ --------------- -------------------
BIN$9Ox0/5P8HKjgQAB/AQAQGg==$0 EMP1 2014-03-19:10:30:33
BIN$9Ox0/5P6HKjgQAB/AQAQGg==$0 EMP1 2014-03-19:10:30:08
BIN$9Ox0/5P7HKjgQAB/AQAQGg==$0 EMP1 2014-03-19:10:30:19
SQL> flashback table emp1 to before drop;
Flashback complete.
此时执行闪回表操作会发现恢复的是最近一次删除的表
SQL> select OBJECT_NAME,ORIGINAL_NAME,DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ --------------- -------------------
BIN$9Ox0/5P6HKjgQAB/AQAQGg==$0 EMP1 2014-03-19:10:30:08
BIN$9Ox0/5P7HKjgQAB/AQAQGg==$0 EMP1 2014-03-19:10:30:19
SQL> flashback table emp1 to before drop;
flashback table emp1 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
由于表名已经存在,无法恢复为原来的表,当然也可以指定恢复特定时刻删除的表,这是就需要指定删除时数据库生成的唯一新表名了(由于前面已经恢复了emp1,需恢复时重命名一下表名):
SQL> flashback table "BIN$9Ox0/5P6HKjgQAB/AQAQGg==$0" to before drop rename to emp2;
Flashback complete.
从UNDO表空间中恢复:
闪回查询也能达到该效果,只是每次恢复时都要对以前的操作进行分析,还要执行insert,update等dml语句,相对来说比较麻烦,闪回表就简单的多(注意利用flashback table恢复表中数据时需要开启ROW MOVEMENT):
查看表的Row Movement是否开启:
SQL> select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
DEPT DISABLED
EMP DISABLED
BONUS DISABLED
SALGRADE DISABLED
EMP1 DISABLED
启动emp1的row movement
SQL> alter table emp1 enable row movement;
Table altered.
SQL> select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
DEPT DISABLED
EMP DISABLED
BONUS DISABLED
SALGRADE DISABLED
EMP1 ENABLED
基于SCN(或者timestamp也可以,一个道理)的闪回表:
SQL> select count(*) from emp1;
COUNT(*)
----------
14
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
512627
SQL> delete from emp1
2 where rownum
3 /
9 rows deleted.
SQL> select count(*) from emp1;
COUNT(*)
----------
5
SQL> flashback table emp1 to scn 512627;
Flashback complete.
SQL> select count(*) from emp1;
COUNT(*)
----------
14
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1124904/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29320885/viewspace-1124904/