共計 3845 個字符，預計需要花費 10 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下數據庫中閃回有哪些，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
閃回主要有閃回表、閃回查詢、閃回數據庫 (數據來自閃回日志) 三種，這里主要了解閃回表和閃回查詢
閃回表：又分以下 2 種：
Flashback table tablename to before drop(數據來自回收站)
Flashback table tablename to scn/timestamp(數據來自 UNDO，必須開啟 Row movement)
閃回查詢：又分以下 3 種
閃回查詢 AS OF SCN/TIMESTAMP(數據來自 UNDO)
閃回版本查詢 Flashback Version Query(數據來自 UNDO)
閃回事務查詢 Flashback Transaction Query(數據來自 UNDO)
閃回表中關于 Row movement 和 Recyclebin 的知識點
The row_movement_clause lets you specify whether the database can move a table row. Specify ENABLE to allow the database to move a row, thus changing the rowid.
Row movement must be enabled for all tables in the Flashback list unless you are flashing back the table TO BEFORE DROP. That operation is called a flashback drop operation, and it uses dropped data in the recycle bin rather than undo data.
row_movement_clause 允許您指定數據庫是否可以移動表行。指定 ENABLE 以允許數據庫移動一行，從而更改 rowid。
閃回必須開啟行移動，但是閃回 DROP 不需要開啟 Row movement，且數據來自回收站
Flashback table 后不需要提交，直接就自動提交了
Flashback table user_tables.TABLE_NAME to before drop(user_tables.TABLE_NAME 就是 recyclebin.ORIGINAL_NAME)
Flashback table recyclebin.OBJECT_NAME to before drop(當一個 table 刪除兩次時，可以指定回收站里名稱按順序指定回收哪次刪除的表)
Flashback table TABLE_NAME to before drop rename to TABLE_NAME2(當刪除一個表后，又建立了一張同名表時，可以使用 rename to 恢復成其他名稱的表)
Purge recyclebin 只是清空當前用戶的回收站
Purge dba_recyclebin 清空所有用戶的回收站
閃回表、閃回查詢的一個示例
SQL create table hr.test1(id number primary key,name varchar2(10));
Table created.
SQL begin
2 insert into test1 values(1, A
3 insert into test1 values(2, B
4 insert into test1 values(3, C
5 commit;
6 dbms_lock.sleep(5);
7 update test1 set name= C-1 where id=3;
8 insert into test1 values(4, D
9 commit;
10 end;
11 /
PL/SQL procedure successfully completed.
– 閃回版本查詢
SQL select versions_startscn,versions_endscn,versions_xid,versions_operation,name from test1 versions BETWEEN SCN MINVALUE AND MAXVALUE;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID VE NAME
—————– ————— —————- — ——————–
4083242 03002000610D0000 I D
4083242 03002000610D0000 U C-1
4083235 4083242 01000400970C0000 I C
4083235 01000400970C0000 I B
4083235 01000400970C0000 I A
– 閃回查詢 AS OF SCN
SQL select * from test1 as of scn 4083235;
ID NAME
———- ——————–
1 A
2 B
3 C
– 閃回版本查詢
SQL select versions_startscn,versions_endscn,versions_xid,versions_operation,name from test1 versions BETWEEN TIMESTAMP TO_TIMESTAMP(2017-08-16 14:17:10 , YYYY-MM-DD HH24:MI:SS) AND TO_TIMESTAMP(2017-08-16 14:19:26 , YYYY-MM-DD HH24:MI:SS
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID VE NAME
—————– ————— —————- — ——————–
4083242 03002000610D0000 I D
4083242 03002000610D0000 U C-1
4083235 4083242 01000400970C0000 I C
4083235 01000400970C0000 I B
4083235 01000400970C0000 I A
– 閃回查詢 AS OF TIMESTAMP
SQL select * from test1 AS OF TIMESTAMP TO_TIMESTAMP(2017-08-16 14:17:16 , YYYY-MM-DD HH24:MI:SS
ID NAME
———- ——————–
1 A
2 B
3 C
– 閃回事務查詢
SQL SELECT undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW(03002000610D0000
UNDO_SQL
——————————————————————————–
delete from HR . TEST1 where ROWID = AAAWV2AAEAAAAJXAAD
update HR . TEST1 set NAME = C where ROWID = AAAWV2AAEAAAAJXAAC
SQL select * from test1;
ID NAME
———- ——————–
1 A
2 B
3 C-1
4 D
– 啟用 ROW MOVEMENT
SQL ALTER TABLE test1 ENABLE ROW MOVEMENT;
Table altered.
–Flashback table to SCN
SQL flashback table test1 to scn 4083235;
Flashback complete.
SQL select * from test1;
ID NAME
———- ——————–
1 A
2 B
3 C
SQL drop table test1;
Table dropped.
–Flashback table to BEFORE DROP
SQL FLASHBACK TABLE test1 TO BEFORE DROP;
Flashback complete.
SQL drop table test1;
Table dropped.
–Flashback table to BEFORE DROP RENAME
SQL FLASHBACK TABLE test1 TO BEFORE DROP RENAME TO test1_2017;
Flashback complete.
SQL select * from test1_2017;
ID NAME
———- ——————–
1 A
2 B
3 C
以上是“數據庫中閃回有哪些”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
