Oracle Flashback Table Usage Example

Make sure RecycleBin is open.

Sql> Show Parameter RecycleBin

NAME TYPE VALUE

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

RecycleBin string on

Create a table

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.

Delete Table 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

found that the index on the TAB01 was also 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 ';

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Index_name STATUS

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

idx_id VALID

You can also specify the name of the RecycleBin if you delete the same table multiple times 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 with an ID value of 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 at the same time 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

You can also perform a table-level point-in-time recovery based on timestamp or SCN, and you need to open 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