【标题】：Oracle 9i中FlashBack闪回查询操作实例
【关键字】：Oracle,9i,FlashBack
【来源】：http://blog.chinaunix.net/article.php?articleId=61667&blogId=6797

Oracle 9i中FlashBack闪回查询操作实例

1.查询

SQL> select * from gametable ;

2.删除

SQL> delete from gametable where GAMEID=1008;

1 row deleted.

SQL> commit;

Commit complete.

3.使用FlashBack查询(过5分钟)

SQL> select GAMEID from gametable AS OF TIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')
3 WHERE GAMEID=1008;

4.使用FlashBack查询恢复表行数据

SQL> INSERT INTO gametable SELECT * FROM gametable AS OF TIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')
3 WHERE GAMEID=1008;

1 row created.

SQL> COMMIT;

Commit complete.

Oracle 9i中FlashBack闪回查询操作实例

　　进行闪回查询必须设置自动回滚段管理，在init.ora设置参数UNDO_MANAGEMENT=AUTO，参数UNDO_RETENTION=n，决定了能往前闪回的最大

时间，值越大就需要越多Undo空间。

*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

1.查询

SQL> select * from gametable ;

      GAMEID NAME                                           STATUS CREATIOND
------------ ---------------------------------------- ------------ ---------
    HTTPPORT HTTPADDRESS               TCPPORT
------------ -------------------- ------------
DESCRIPTION
--------------------------------------------------------------------------------
        1008 帝国时代                                                0 26-MAR-05
           0                                 0

        1009 麻将                                            0 26-MAR-05
           0                                 0

2 rows selected.

2.删除

SQL> delete from gametable where GAMEID=1008;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from gametable ;

      GAMEID NAME                                           STATUS CREATIOND
------------ ---------------------------------------- ------------ ---------
    HTTPPORT HTTPADDRESS               TCPPORT
------------ -------------------- ------------
DESCRIPTION
--------------------------------------------------------------------------------
        1009 麻将                                            0 26-MAR-05
           0                                 0

2 rows selected.

3.使用FlashBack查询(过5分钟)

SQL> select GAMEID from gametable AS OF TIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')
3 WHERE GAMEID=1008;

      GAMEID
------------
        1008

SQL> select GAMEID from gametable ;

      GAMEID
------------
        1009

2 rows selected.

4.使用FlashBack查询恢复表行数据

SQL> INSERT INTO gametable SELECT * FROM gametable AS OF TIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DD HH24:MI:SS')
3 WHERE GAMEID=1008;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select GAMEID from gametable ;

      GAMEID
------------
        1009
        1008

2 rows selected.

SQL> select * from gametable ;

      GAMEID NAME                                           STATUS CREATIOND
------------ ---------------------------------------- ------------ ---------
    HTTPPORT HTTPADDRESS               TCPPORT
------------ -------------------- ------------
DESCRIPTION
--------------------------------------------------------------------------------
        1009 麻将                                            0 26-MAR-05
           0                                 0

        1008 帝国时代                                                0 26-MAR-05
           0                                 0

      GAMEID NAME                                           STATUS CREATIOND
------------ ---------------------------------------- ------------ ---------
    HTTPPORT HTTPADDRESS               TCPPORT
------------ -------------------- ------------
DESCRIPTION
--------------------------------------------------------------------------------

2 rows selected.

SQL>