Oracle Flashback table使用樣本

確保recyclebin是開啟的

SQL> show parameter recyclebin

NAME                                 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

----------

1

SQL> 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_ID

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

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

----------

1

18: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';

更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

INDEX_NAME                     STATUS

------------------------------ --------

IDX_ID                         VALID

如果多次刪除同一張表也可以指定recyclebin的名稱flashback

18:25:29 SQL> select * from tab01;

ID

----------

1

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

----------

2

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

----------

3

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

----------

1

2

3

4

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

----------

1

2

3

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

4

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