DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
TEMP TABLE
FLASH_TBL TABLE
TEST TABLE
8 rows selected.
SQL> drop table temp;
Table dropped.
SQL> create table temp as select * from flash_tbl;
Table created.
SQL> flashback table temp to before drop;
flashback table temp to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> flashback table temp to before drop rename to old_temp; 命名冲突，重命名……
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
OLD_TEMP TABLE
FLASH_TBL TABLE
TEST TABLE
TEMP TABLE
9 rows selected.
SQL> select * from old_temp;
ID
----------
1
2
3
从多次删除中恢复：
SQL> create table temp_old as select * from temp;
Table created.
SQL> drop table temp;
Table dropped.
SQL> alter table temp_old rename to temp;
Table altered.
SQL> drop table temp;
Table dropped.
SQL> create table temp (id int);
Table created.
SQL> drop table temp;
Table dropped.
SQL> select droptime,original_name,object_name from recyclebin;
DROPTIME ORIGINAL_NAME
------------------- --------------------------------
OBJECT_NAME
------------------------------
2014-04-01:04:48:58 TEMP
BIN$9fm3y2lF6oHgQAB/AQAeMQ==$0
2014-04-01:04:49:26 TEMP
BIN$9fm3y2lG6oHgQAB/AQAeMQ==$0
2014-04-01:04:46:26 TEMP
BIN$9fm3y2lE6oHgQAB/AQAeMQ==$0
SQL> flashback table temp to before drop;
Flashback complete.
SQL> select droptime,original_name,object_name from recyclebin;
DROPTIME ORIGINAL_NAME
------------------- --------------------------------
OBJECT_NAME
------------------------------
2014-04-01:04:48:58 TEMP
BIN$9fm3y2lF6oHgQAB/AQAeMQ==$0
2014-04-01:04:46:26 TEMP
BIN$9fm3y2lE6oHgQAB/AQAeMQ==$0
从时间可以看出越后删除的越先被恢复，即倒着恢复。
SQL> select droptime,original_name,object_name from recyclebin;
DROPTIME ORIGINAL_NAME
------------------- --------------------------------
OBJECT_NAME
------------------------------
2014-04-01:05:04:31 TEMP3
BIN$9fm3y2lI6oHgQAB/AQAeMQ==$0
2014-04-01:05:04:27 TEMP2
BIN$9fm3y2lH6oHgQAB/AQAeMQ==$0
当然可以直接指定对象名称来恢复：
SQL> flashback table "BIN$9fm3y2lI6oHgQAB/AQAeMQ==$0" to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EXPFULL TABLE
BIN$9fm3y2lH6oHgQAB/AQAeMQ==$0 TABLE
FLASH_TBL TABLE
TEST TABLE
TEMP3 TABLE
TEMP TABLE
10 rows selected.
从UNDO表空间恢复：
SQL> select * from flash_tbl;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
117 P
118 Q
119 R
120 S
6 E
ID V
---------- -
7 F
8 G
9 H
300 r
500 t
16 rows selected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1227868
SQL> delete flash_tbl where id=7;
1 row deleted.
SQL> insert into flash_tbl values(25,'r');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from flash_tbl;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
117 P
118 Q
119 R
120 S
25 r
ID V
---------- -
6 E
8 G
9 H
300 r
500 t
16 rows selected.
基于SCN的查询：
SQL> select * from flash_tbl as of scn 1227868;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
117 P
118 Q
119 R
120 S
6 E
ID V
---------- -
7 F
8 G
9 H
300 r
500 t
16 rows selected.
SQL> flashback table flash_tbl to scn 1227868;
flashback table flash_tbl to scn 1227868
*
ERROR at line 1:
ORA-08189: cannot flashback the t