Oracle Flashback table的使用

本文主要是介绍Oracle Flashback table的使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

确保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==$0

rename 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的名称flashback

18: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 exist

18: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:50

flashback 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 movement

18: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
----------1234

18: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
----------12318: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
----------123418: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
----------123

这篇关于Oracle Flashback table的使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---