flashback閃回技術應用解析
閃回技術中包括:閃回查詢,閃回表,閃回刪除表,下面一一細說
一:閃回查詢,就是如果你對一個表做了DML,並且已經commit,但是你想檢視你修改之前的值,這時你就可以用閃回查詢了
1,基於時間的閃回查詢,
HR@ENMOEDU> select employee_id,first_name,last_name
2 from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
2 from employees;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
HR@ENMOEDU> select employee_id,first_name,last_name from employees as of timestamp sysdate - 1/1440; #檢視一分鐘前的表的資訊 109 號記錄 還是有的。
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
2.基於scn的閃回查詢,
SYS@ENMOEDU> grant execute on dbms_flashback to hr; #先授予hr使用者來查詢scn號的許可權
Grant succeeded.
SYS@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU> select dbms_flashback.get_system_change_number from dual; # 查詢當前的scn號
GET_SYSTEM_CHANGE_NUMBER
------------------------
1235204
HR@ENMOEDU> select dbms_flashback.get_system_change_number from dual; # 查詢當前的scn號
GET_SYSTEM_CHANGE_NUMBER
------------------------
1235204
HR@ENMOEDU> delete from employees where employee_id=107;
1 row deleted.
HR@ENMOEDU> select employee_id,last_name from employees e
2 where e.employee_id <=110; #發現已經沒有了107號
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
1 row deleted.
HR@ENMOEDU> select employee_id,last_name from employees e
2 where e.employee_id <=110; #發現已經沒有了107號
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
105 David
106 Pataballa
108 Greenberg
110 Chen
9 rows selected.
106 Pataballa
108 Greenberg
110 Chen
9 rows selected.
HR@ENMOEDU> select employee_id,last_name from employees as of scn 1235204 where employee_id <=110; #發現又有的107號
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
EMPLOYEE_ID LAST_NAME
----------- -------------------------
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
105 David
106 Pataballa
107 Lorentz
108 Greenberg
110 Chen
106 Pataballa
107 Lorentz
108 Greenberg
110 Chen
10 rows selected.
3.基於版本的閃回查詢:
HR@ENMOEDU> update employees set last_name ='liuwen' where employee_id=202;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='liuwenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='wenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU>commit ;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='liuwenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU> update employees set last_name ='wenhe' where employee_id=202;
1 row updated.
HR@ENMOEDU>commit ;
Commit complete.
HR@ENMOEDU> select employee_id,last_name,versions_starttime,versions_endtime,versions_operation
2 from employees
3 versions between scn minvalue and maxvalue
4 where employee_id=202;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
202 wenhe
12-AUG-14 08.40.59 PM
U
202 liuwenhe
12-AUG-14 08.40.49 PM
12-AUG-14 08.40.59 PM U
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
202 liuwen
12-AUG-14 08.40.40 PM
12-AUG-14 08.40.49 PM U
202 Fay
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
12-AUG-14 08.40.40 PM
HR@ENMOEDU> select employee_id,last_name,versions_starttime,versions_endtime,versions_operation
2 from employees
3 versions between scn minvalue and maxvalue
4 where employee_id=202;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
202 wenhe
12-AUG-14 08.40.59 PM
U
202 liuwenhe
12-AUG-14 08.40.49 PM
12-AUG-14 08.40.59 PM U
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
202 liuwen
12-AUG-14 08.40.40 PM
12-AUG-14 08.40.49 PM U
202 Fay
EMPLOYEE_ID LAST_NAME
----------- -------------------------
VERSIONS_STARTTIME
---------------------------------------------------------------------------
VERSIONS_ENDTIME V
--------------------------------------------------------------------------- -
12-AUG-14 08.40.40 PM
二:閃回表 是說 你只是刪掉了表的一行或多行資料,但是表結構還在,這時候 你是可以閃回到你沒有刪資料的狀態的,
HR@ENMOEDU> select * from liu;
X
----------
2
1
HR@ENMOEDU> delete from liu where x=1;
1 row deleted.
HR@ENMOEDU> select * from liu;
X
----------
2
X
----------
2
1
HR@ENMOEDU> delete from liu where x=1;
1 row deleted.
HR@ENMOEDU> select * from liu;
X
----------
2
HR@ENMOEDU> flashback table liu to timestamp sysdate-5/1440;
flashback table liu to timestamp sysdate-5/1440
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled #是因為你的錶行遷移開關未開啟
flashback table liu to timestamp sysdate-5/1440
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled #是因為你的錶行遷移開關未開啟
然後再執行閃回表
HR@ENMOEDU> flashback table liu to timestamp sysdate-8/1440;
Flashback complete.
HR@ENMOEDU> select * from liu; #發現表確實又回到了刪資料之前了
X
----------
2
1
三:閃回刪除表: 可以理解為恢復,
HR@ENMOEDU> create table t (x int);
Table created.
HR@ENMOEDU> insert into t values(2);
1 row created.
HR@ENMOEDU> commit
2 ;
Commit complete.
HR@ENMOEDU> drop table t;
Table dropped.
HR@ENMOEDU> flashback table t to before drop rename to liu; 閃回並重新命名;
Flashback complete.
HR@ENMOEDU> select * from liu;
X
----------
2
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1349451/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Flashback閃回技術
- Oracle閃回技術--Flashback Version QueryOracle
- Oracle Database 11g閃回技術flashbackOracleDatabase
- 【Flashback】使用Flashback Drop技術閃回被DROP表的指定版本資料
- oracle 閃回 flashbackOracle
- 閃回技術二:閃回表
- 【Flashback】啟用Flashback Database閃回資料庫功能Database資料庫
- 閃回技術一:閃回查詢
- 閃回表技術
- Oracle閃回技術 概覽 應用程式開發功能Oracle
- Oracle閃回技術之閃回資料庫Oracle資料庫
- flashback query閃回資料
- Flashback Query閃回查詢
- Oracle 閃回特性(FLASHBACK DATABASE)OracleDatabase
- 閃回刪除技術:
- [閃回特性之閃回版本查詢]Flashback Version Query
- [Flashback]Flashback Database閃回資料庫實驗Database資料庫
- 【Flashback】Flashback Drop閃回刪除功能實踐
- Flashback Database 閃回資料庫Database資料庫
- 【Mysql】mysql閃回flashback-5.7MySql
- 啟用Flashback Database閃回資料庫功能(閃回區滿解決辦法 )Database資料庫
- 閃回版本查詢技術:
- 閃回技術全瞭解
- oracle 閃回技術簡介Oracle
- oracke閃回技術總結
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- 【備份恢復】 閃回技術之閃回刪除
- Oracle 閃回技術 概覽 資料庫閃回功能Oracle資料庫
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- 【Flashback】Flashback Database閃回資料庫功能實踐Database資料庫
- 11R2-DataGuard Scenarios.DG中應用閃回技術iOS
- FlashBack總結之閃回資料庫與閃回刪除資料庫
- 【備份恢復】閃回技術之閃回版本查詢
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- Oracle 11G 閃回技術 使用閃回版本查詢Oracle
- 利用flashback閃回表和資料
- 開啟oracle的flashback閃回功能Oracle
- Flashback_oracle閃回功能的使用Oracle