Flashback Table闪回表

来源：互联网发布：大氛围大网络大格局编辑：程序博客网时间：2024/05/28 15:41

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;

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

0 0