oracle資料庫恢復
1、根據時間點去找表的快照
select * from sj_jbsj
AS OF TIMESTAMP TO_TIMESTAMP('2013-12-13 16:10:25','YYYY-MM-DDHH24:MI:SS');
2、將快照創建到一張緩存表中
create table guohaotemp2 as
select * from sj_jbsj
AS OF TIMESTAMP TO_TIMESTAMP('2013-12-13 16:10:25','YYYY-MM-DDHH24:MI:SS')
3、將表中的數據更新為緩存表中是數據
update sj_jbsj set jkzzb=(select jkzzb from guohaotemp2 whereguohaotemp2.jh=sj_jbsj.jh)
Advertisements
ok
參考：
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 * from tdcs_layout AS OF TIMESTAMPTO_TIMESTAMP('2009-04-16 09:10:25','YYYY-MM-DD HH24:MI:SS');
4.使用FlashBack查詢恢復錶行數據
Advertisements
SQL> INSERT INTO gametable SELECT * FROM gametable AS OFTIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DDHH24: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-DDHH24: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 OFTIMESTAMP
2 TO_TIMESTAMP('2005-12-15 14:37:25','YYYY-MM-DDHH24: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>
