Oracle Flashback table的使用

Oracle Flashback table的使用Oracle确保recyclebin是打开的
SQL> show parameter recyclebinNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string ON创建一张表
SQL> create table tab01（id int）;Table created.SQL> insert into tab01 values（1）;1 row created.SQL> commit;Commit complete.SQL> select * from tab01; ID
----------
1SQL> create index ind_id on tab01（id）;Index created.删除表TAB01
18:18:26 SQL> select index_name from ind where table_name="TAB01";INDEX_NAME
------------------------------
IND_ID18:18:33 SQL> drop table tab01;Table dropped.18:18:41 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB01 BIN$7E8nf4eZQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:18:41
18:18:43 SQL> select index_name from ind where table_name="TAB01";no rows selected18:18:50 SQL> select * from tab01;
select * from tab01
*
ERROR at line 1:
ORA-00942: table or view does not exist
发现TAB01上的index也被rename，flashback TAB01
18:19:41 SQL> flashback table tab01 to before drop;Flashback complete.18:19:51 SQL> select * from tab01; ID
----------
118:19:54 SQL> select index_name from ind where table_name="TAB01";INDEX_NAME
------------------------------
BIN$7E8nf4eYQZzgQKjACzgfMg==$0rename index
18:23:09 SQL> ALTER INDEX "BIN$7E8nf4eYQZzgQKjACzgfMg==$0" RENAME TO IDX_ID;Index altered.18:23:45 SQL> select index_name,status from ind where table_name="TAB01";INDEX_NAME STATUS
------------------------------ --------
IDX_ID VALID 如果多次删除同一张表也可以指定recyclebin的名称flashback18:25:29 SQL> select * from tab01; ID
----------
118:25:36 SQL> drop table tab01;Table dropped.18:25:50 SQL> create table tab01（id int）;Table created.18:26:17 SQL> insert into tab01 values （2）;1 row created.18:26:30 SQL> commit;Commit complete.18:26:33 SQL> select * from tab01; ID
----------
218:26:37 SQL> drop table tab01;Table dropped.18:26:43 SQL> create table tab01（id int）;Table created.18:26:46 SQL> insert into tab01 values（3）;1 row created.18:26:55 SQL> commit;Commit complete.18:26:56 SQL> select * from tab01; ID
----------
318:26:59 SQL> drop table tab01;Table dropped.18:27:02 SQL> select * from tab01;
select * from tab01
*
ERROR at line 1:
ORA-00942: table or view does not exist18:27:10 SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TAB01 BIN$7E8nf4edQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:27:02
TAB01 BIN$7E8nf4ecQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:26:43
TAB01 BIN$7E8nf4ebQZzgQKjACzgfMg==$0 TABLE 2013-11-29:18:25:50flashback tab01中id值为2的
18:27:51 SQL> flashback table "BIN$7E8nf4ecQZzgQKjACzgfMg==$0" to before drop;Flashback complete.18:29:17 SQL> select * from tab01; ID
----------
2
flashback同时rename
18:30:54 SQL> flashback table "BIN$7E8nf4edQZzgQKjACzgfMg==$0" to before drop rename to tab02;Flashback complete.18:31:17 SQL> select * from tab02; ID
----------
3
也可以根据timestamp或者scn进行表级别的基于时间点的恢复，需要开启row movement18:32:42 SQL> create table tab03（id int）;Table created.18:32:55 SQL> insert into tab03 values（1）;1 row created.18:33:08 SQL> insert into tab03 values（2）;1 row created.18:33:10 SQL> insert into tab03 values（3）;1 row created.18:33:12 SQL> commit;Commit complete.18:33:14 SQL>
18:33:16 SQL> insert into tab03 values（4）;1 row created.18:33:23 SQL> commit;Commit complete.18:33:25 SQL> select * from tab03; ID
----------
1
2
3
418:35:33 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp（"2013-11-29 18:33:25","yyyy-mm-dd hh24:mi:ss"）;Flashback complete.18:35:39 SQL> select * from tab03; ID
----------
1
2
318:35:40 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp（"2013-11-29 18:33:30","yyyy-mm-dd hh24:mi:ss"）;Flashback complete.18:35:54 SQL> select * from tab03; ID
----------
1
2
3
418:35:55 SQL> FLASHBACK TABLE TAB03 TO timestamp to_timestamp（"2013-11-29 18:33:25","yyyy-mm-dd hh24:mi:ss"）;Flashback complete.18:35:59 SQL> select * from tab03; ID
----------
1
2
3相关阅读：Oracle 11g Flashback Data Archive（闪回数据归档） http://www.linuxidc.com/Linux/2013-06/86696.htmOracle Flashback闪回机制 http://www.linuxidc.com/Linux/2013-05/84223.htmOracle Flashback database http://www.linuxidc.com/Linux/2013-05/84129.htmFlashback table快速恢复误删除的数据 http://www.linuxidc.com/Linux/2012-09/70988.htmOracle 备份恢复：Flashback闪回 http://www.linuxidc.com/Linux/2012-09/69958.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12MongoDB 磁盘空间不足Oracle记录用户DDL操作脚本相关资讯 Oracle flashback Flashback table

Oracle闪回技术flashback （08/20/2015 10:45:10）
Oracle 11g闪回flashback （01/25/2015 19:21:34）
Oracle实例恢复阶段以及flashback （09/17/2014 17:27:13）

Oracle 11g开启闪回功能Flashback （06/22/2015 12:02:15）
Oracle DB闪回（Flashback （09/26/2014 19:42:38）
Oracle Flashback基础应用（04/06/2014 16:14:56）

本文评论查看全部评论（0）