閃回主要有閃回表、閃回查詢、閃回資料庫(資料來自閃回日誌)三種，這裡主要了解閃回表和閃回查詢

閃回表：又分以下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

閃回表、閃回查詢

相關文章