Oracle10g里面有个Recyclebin，删除时没加purge参数并非真正删除，而是先通过修改数据字典的方式，并将其改名放入Recycle Bin中。
从Recycle Bin 中恢复：
1.创建测试数据
SQL> conn scott/oracle
Connected.
SQL> create table temp(id int primary key);
Table created.
SQL> insert into temp values(1);
1 row created.
SQL> insert into temp values(2);
1 row created.
SQL> insert into temp values(3);
1 row created.
SQL> drop table temp;
Table dropped.
2.查看Recycle Bin
SQL> desc recyclebin;
?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- ----------------------------
?OBJECT_NAME?????????????????????????????? NOT NULL VARCHAR2(30)
?ORIGINAL_NAME????????????????????????????????????? VARCHAR2(32)
?OPERATION????????????????????????????????????????? VARCHAR2(9)
?TYPE?????????????????????????????????????????????? VARCHAR2(25)
?TS_NAME??????????????????????????????????????????? VARCHAR2(30)
?CREATETIME???????????????????????????????????????? VARCHAR2(19)
?DROPTIME?????????????????????????????????????????? VARCHAR2(19)
?DROPSCN??????????????????????????????????????????? NUMBER
?PARTITION_NAME???????????????????????????????????? VARCHAR2(32)
?CAN_UNDROP???????????????????????????????????????? VARCHAR2(3)
?CAN_PURGE????????????????????????????????????????? VARCHAR2(3)
?RELATED?????????????????????????????????? NOT NULL NUMBER
?BASE_OBJECT?????????????????????????????? NOT NULL NUMBER
?PURGE_OBJECT????????????????????????????? NOT NULL NUMBER
?SPACE????????????????????????????????????????????? NUMBER
SQL> select ORIGINAL_NAME,OBJECT_NAME from recyclebin;
ORIGINAL_NAME??????????????????? OBJECT_NAME
-------------------------------- ------------------------------
TEMP???????????????????????????? BIN$9fpGUw34tFvgQAB/AQAb+w==$0 //TEMP表
SYS_C006287????????????????????? BIN$9fpGUw33tFvgQAB/AQAb+w==$0 //主键
SQL> select * from tab;
TNAME????????????????????????? TABTYPE? CLUSTERID
------------------------------ ------- ----------
DEPT?????????????????????????? TABLE
EMP??????????????????????????? TABLE
BONUS????????????????????????? TABLE
SALGRADE?????????????????????? TABLE
EXPFULL??????????????????????? TABLE
FLASH_TBL????????????????????? TABLE
TEST?????????????????????????? TABLE
BIN$9fpGUw34tFvgQAB/AQAb+w==$0 TABLE
3.恢复表
SQL> flashback table temp to before drop;
Flashback complete.
SQL> select * from tab;
TNAME????????????????????????? TABTYPE? CLUSTERID
------------------------------ ------- ----------
DEPT?????????????????????????? TABLE
EMP??????????????????????????? TABLE
BONUS????????????????????????? TABLE
SALGRADE?????????????????????? TABLE
EXPFULL??????????????????????? TABLE
TEMP?????????????????????????? TABLE
FLASH_TBL????????????????????? TABLE
TEST?????????????????????????? TABLE
SQL> select * from temp;
??????? ID
----------
???????? 1
???????? 2
???????? 3
SQL> desc user_indexes;
?Name????????????????????????????????????? Null???? Type
?----------------------------------------- -------- ----------------------------
?INDEX_NAME??????????????????????????????? NOT NULL VARCHAR2(30)
?INDEX_TYPE???????????????????????????????????????? VARCHAR2(27)
?TABLE_OWNER?????????????????????????????? NOT NULL VARCHAR2(30)
?TABLE_NAME??????????????????????????????? NOT NULL VARCHAR2(30)
?TABLE_TYPE???????????????????????????????????????? VARCHAR2(11)
?UNIQUENESS???????????????????????????????????????? VARCHAR2(9)
?COMPRESSION??????????????????????????????????????? VARCHAR2(8)
?PREFIX_LENGTH????????????????????????????????????? NUMBER
?TABLESPACE_NAME??????????????????????????????????? VARCHAR2(30)
?INI_TRANS????????????????????????????????????????? NUMBER
?MAX_TRANS????????????????????????????????????????? NUMBER
?INITIAL_EXTENT???????????????????????????????????? NUMBER
?NEXT_EXTENT??????????????????????????????????????? NUMBER
?MIN_EXTENTS??????????????????????????????????????? NUMBER
?MAX_EXTENTS??????????????????????????????????????? NUMBER
?PCT_INCREASE?????????????????????????????????????? NUMBER
?PCT_THRESHOLD????????????????????????????????????? NUMBER
?INCLUDE_COLUMN???????????????????????????????????? NUMBER
?FREELISTS????????????????????????????????????????? NUMBER
?FREELIST_GROUPS??????????????????????????????????? NUMBER
?PCT_FREE?????????????????????????????????????????? NUMBER
?LOGGING??????????????????????????????????????????? VARCHAR2(3)
?BLEVEL???????????????????????????????????????????? NUMBER
?LEAF_BLOCKS??????????????????????????????????????? NUMBER
?DISTINCT_KEYS????????????????????????????????????? NUMBER
?AVG_LEAF_BLOCKS_PER_KEY??????????????????????????? NUMBER
?AVG_DATA_BLOCKS_PER_KEY??????????????????????????? NUMBER
?CLUSTERING_FACTOR????????????????????????????????? NUMBER
?STATUS???????????????????????????????????????????? VARCHAR2(8)
?NUM_ROWS?????????????????????????????????????????? NUMBER
?SAMPLE_SIZE??????????????????????????????????????? NUMBER
?LAST_ANALYZED????????????????????????????????????? DATE
?DEGREE???????????????????????????????????????????? VARCHAR2(40)
?INSTANCES????????????????????????????????????????? VARCHAR2(40)
?PARTITIONED??????????????????????????????????????? VARCHAR2(3)
?TEMPORARY????????????????????????????????????????? VARCHAR2(1)
?GENERATED????????????????????????????????????????? VARCHAR2(1)
?SECONDARY????????????????????????????????????????? VARCHAR2(1)
?BUFFER_POOL??????????????????????????????????????? VARCHAR2(7)
?USER_STATS???????????????????????????????????????? VARCHAR2(3)
?DURATION?????????????????????????????????????????? VARCHAR2(15)
?PCT_DIRECT_ACCESS????????????????????????????????? NUMBER
?ITYP_OWNER???????????????????????????????????????? VARCHAR2(30)
?ITYP_NAME????????????????????????????????????????? VARCHAR2(30)
?PARAMETERS???????????????????????????????????????? VARCHAR2(1000)
?GLOBAL_STATS?????????????????????????????????????? VARCHAR2(3)
?DOMIDX_STATUS????????????????????????????????????? VARCHAR2(12)
?DOMIDX_OPSTATUS??????????????????????????????????? VARCHAR2(6)
?FUNCIDX_STATUS???????????????????????????????????? VARCHAR2(8)
?JOIN_INDEX???????????????????????????????????????? VARCHAR2(3)
?IOT_REDUNDANT_PKEY_ELIM??????????????????????????? VARCHAR2(3)
?DROPPED??????????????????????????????????????????? VARCHAR2(3)
SQL> select table_name,index_name from user_indexes where table_name='TEMP';
TABLE_NAME???????????????????? INDEX_NAME
------------------------------ ------------------------------
TEMP?????????????????????????? BIN$9fpGUw33tFvgQAB/AQAb+w==$0
4.手动修改索引
SQL> alter index "BIN$9fpGUw33tFvgQAB/AQAb+w==$0" rename to SYS_C006287;
Index altered.
复杂点的表恢复：
SQL> select * from tab;
TNAME????????????????????????? TABTYPE? CLUSTERID
------------------------------ ------- ----------
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,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,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 table because row movement is not enabled
SQL> oerr ora 08189
SP2-0734: unknown command beginning "oerr ora 0..." - rest of line ignored.
SQL> host oerr ora 08189
08189,00000,"cannot flashback the table because row movement is not enabled"
// *Cause: An attempt was made to perform Flashback Table operation on a table for
//???????? which row movement has not been enabled. Because the Flashback Table
//???????? does not preserve the rowids,it is necessary that row
//???????? movement be enabled on the table.
// *Action: Enable row movement on the table
未开启行移动
SQL> alter table flash_tbl enable row movement;
Table altered.
SQL> flashback table flash_tbl to scn 1227868;
Flashback 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
???????? 6 E
??????? ID V
---------- -
???????? 7 F
???????? 8 G
???????? 9 H
?????? 300 r
?????? 500 t
16 rows selected.
?恢复成功……
SCN：在10g中，系统平均每3秒产生一次系统时间与SCN的匹配并存入SYS.SMON_SCN_TIME表，若使用AS OF TIMESTAMP 查询UNDO中的数据，实际获取的数据是以指定的时间对应的SCN时的数据为基准。
例如：SCN?? TIME
??????????? 123???? 2013-04-01 20:25:00
??????????? 125???? 2013-04-01 20:30:00
当通过AS OF TIMESTAMP查询2013-04-01 20:25:00或2013-04-01 20:29:59这段时间内任何时间，Oracle都会将其匹配为SCN：123到UNDO表空间中查找。就是说在这个时间段内，查询返回的数据都是2013-04-01 20:25:00这个时间对应的SCN的数据。
