FlashbackTable闪回表

Oracle10g里面有个Recyclebin，删除时没加purge参数并非真正删除，而是先通过修改数据字典的方式，并将其改名放入Recycle Bin中。 从Recycle Bin 中恢复： 1.创建测试数据 SQL conn scott/oracle Connected. SQL create table temp(id int primary key); Tab

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,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 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的数据。