轉自專欄

閃回(Flashback)

SQL> /*

SQL> 錯誤操作：

SQL> 1. 錯誤的delete一條記錄，並且commit

SQL> 2. 錯誤地刪除了一個表: drop table

SQL> 3. 查詢某個錶的歷史記錄(所有已經提交了的歷史記錄)

SQL> 4. 錯誤地執行了一個事務

SQL>

SQL> 對應閃回的四種型別:

SQL> 1. 閃回表

SQL> 2. 閃回刪除

SQL> 3. 閃回版本查詢

SQL> 4. 閃回事務查詢

SQL> 5. 閃回資料庫

SQL> */

SQL> host cls

SQL> --閃回的時間引數

SQL> conn / as sysdba

已連線。

SQL> show user

USER 為 "SYS"

SQL> show parameters undo;

l閃回表，實際上是將表中的資料快速恢復到過去的一個是焦點或者系統改變號SCN上。實現表的閃回，需要使用到與撤銷表空間相關的undo資訊，透過show parameter undo命令可以瞭解這些資訊。

l

l使用者對錶資料的修改操作，都記錄在撤銷表空間中，這為表的閃回提供了資料恢復的基礎。例如，某個修改操作在提交後被記錄在撤銷表空間中，保留時間為900秒，使用者可以在這900秒的時間內對錶進行閃回操作，從而將表中的資料恢復到修改之前的狀態。

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> -- 將900秒改為20分鐘

SQL> alter system set undo_retention=1200 scope=both;

系統已更改。

SQL> show parameters undo;

NAME TYPE VALUE

undo_management string AUTO

undo_retention integer 1200

undo_tablespace string UNDOTBS1

SQL> /*

SQL> scope的取值: session spfile(引數檔案) both

SQL> */

SQL> --為scott授予閃回的許可權

SQL> grant flashback any table to scott;

授權成功。

SQL> conn scott/tiger

已連線。

SQL> show user

USER 為 "SCOTT"

SQL> host cls

SQL> --第一種型別閃回：閃回表

SQL> create table flashback_table1

2 (fid number,fnamevarchar2(10));

表已建立。

SQL> insert into flashback_table1 values(&fid,'&fname');

輸入 fid 的值: 1

輸入 fname 的值: Tom

SQL> commit;

提交完成。

SQL> select * from flashback_table1;

FID FNAME

1 Tom

2 Mary

3 Mike

SQL> --記錄當前的系統時間(SCN)

SQL> select sysdate 時間, timestamp_to_scn(sysdate) SCN fromdual;

時間 SCN

21-9月 -11 4354761

SQL> delete from flashback_table1 wherefid=2;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table1;

FID FNAME

---------- ----------

1 Tom

3 Mike

SQL> --執行閃回表

SQL> flashback table flashback_table1 toscn 4354761;

flashback table flashback_table1 to scn4354761

*

第 1 行出現錯誤:

ORA-08189: 因為未啟用行移動功能, 不能閃回表

SQL> select rowid,fid,fname fromflashback_table1;

ROWID FID FNAME

------------------ ---------- ----------

AAANh7AAEAAAAGnAAA 1 Tom

AAANh7AAEAAAAGnAAC 3 Mike

SQL> --開啟行移動功能

SQL> alter table flashback_table1 enable row movement;

表已更改。

SQL> flashback table flashback_table1 to scn 4354761;

閃回完成。

SQL> select rowid,fid,fname fromflashback_table1;

ROWID FID FNAME

------------------ --------------------

AAANh7AAEAAAAGnAAB 1 Tom

AAANh7AAEAAAAGnAAD 2 Mary

AAANh7AAEAAAAGnAAE 3 Mike

閃回表語法：

FLASHBACK TABLE [schema.]

TO

{[BEFORE DROP [RENAME TO table]]

[SCN|TIMESTAMP]expr

[ENABLE|DISABLE]TRIGGERS}

lschema：模式名，一般為使用者名稱。

lTO TIMESTAMP：系統郵戳，包含年、月、日、時、分、秒。

lTO SCN：系統更改號，

lENABLE TRIGGERS：表示觸發器恢復以後為enable狀態，而預設為disable狀態。

lTO BEFORE DROP：表示恢復到刪除之前。

lRENAME TO table：表示更換表名。

SQL> --閃回表的思想：將表回退到過去的一個時間上

SQL> --問題：不知道過去時間，怎麼辦？

SQL> host cls

SQL> --閃回刪除

SQL> --Oracle的回收站

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

FLASHBACK_TABLE TABLE

FLASHBACK_TABLE1 TABLE

SYS_TEMP_FBT TABLE

已選擇7行。

刪除表。

drop table FLASHBACK_TABLE;

檢視回收站

show recyclebin;

清空回收站

purge recyclebin;

徹底刪除

drop table FLASHBACK_TABLE1 purge;

回收站只對普通使用者有效

刪除表

drop table test123;

show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

TEST123 BIN$w6NYaQ1jSZ6uzO2jUQ/ALw==$0 TABLE 2011-09-21:10:35:32

SQL> --閃回刪除：從回收站中取回表

SQL> flashback table test123 to before drop;

閃回完成。

SQL> --透過回收站中的名稱閃回刪除

SQL> flashback table"BIN$9JMrb6kbRCON287lDV+5dA==$0" to before drop;

閃回完成。

SQL> host cls

閃回刪除：回收站（recyclebin）簡介

l回收站是所有被刪除物件及其相依物件的邏輯儲存容器，例如當一個表被刪除(drop)時，該表及其相依物件並不會馬上被資料庫徹底刪除，而是被儲存到回收站中。

l

l回收站將使用者執行的drop操作記錄在一個系統表中，也就是將被刪除的物件寫到一個資料字典中。如果確定不再需要該物件，可以使用purge命令對回收站進行清空。

l

l被刪除的物件的名字可能是相同的，例如使用者建立了一個test表，使用drop命令刪除該表後，又建立了一個test表，這時，如果再次刪除該表就會導致向回收站中新增了兩個相同的表。

l

回收站中物件的命名規則

l為了確保新增到回收站中的物件的名稱都是唯一的，系統會對這些儲存到回收站中的物件進行重新命名，重新命名的格式如下：

l

BIN$globalUID$version

l

•其中： BIN表示RECYCLEBIN；globalUID是一個全域性唯一的、24個位元組長的物件，該標識與原物件名沒有任何關係；version指資料庫分配的版本號。

SQL> -- 閃回重名的表

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST123 TABLE

SYS_TEMP_FBT TABLE

已選擇6行。

SQL> drop table TEST123;

表已刪除。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE

SYS_TEMP_FBT TABLE

已選擇6行。

SQL> create table test123(tid number);

表已建立。

SQL> insert into test123 values(1);

已建立 1 行。

SQL> insert into test123 values(2);

已建立 1 行。

SQL> commit;

提交完成。

SQL> drop table test123;

表已刪除。

SQL> --查詢回收站：應該有兩個重名的test123

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------------------------------------- ------------

TEST123 BIN$HDhYGEaXTayAnETFAUoaNw==$0 TABLE 2011-09-21:10:41:59

TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE 2011-09-21:10:41:17

SQL> --閃回這兩個表

SQL> flashback table test123 to beforedrop;

閃回完成。

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

---------------------------------------------- ------------

TEST123 BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE 2011-09-21:10:41:17

SQL> --閃回同名的表，先閃回最後刪除那個

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

BIN$eqIO0J1LQ0G1rYAnIHWrdg==$0 TABLE

SYS_TEMP_FBT TABLE

TEST123 TABLE

已選擇7行。

SQL> flashback table test123 to beforedrop;

flashback table test123 to before drop

*

第 1 行出現錯誤:

ORA-38312: 原始名稱已被現有物件使用

SQL> --重新命名

SQL> flashback table test123 to beforedrop rename to test1234;

閃回完成。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ -----------------

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST1234 TABLE

SYS_TEMP_FBT TABLE

TEST123 TABLE

已選擇7行。

SQL> select * from test123;

TID

----------

1

2

SQL> select * from test1234;

TID TNAME

---------- --------------------

1 Tom

SQL> host cls

閃回刪除: 需要考慮的事情

l閃回刪除對下列表無效:

在SYSTEM 表空間內的表

用精細審計的資料庫或虛擬的私人資料庫

屬於字典管理的表空間

由於空間不足已經被手動或自動刪除的表

l以下依賴不被保護:

點陣圖索引

表之前刪掉的索引

lFLASHBACK TABLE命令作為單一的事務執行，會得到一個單一的DML鎖

表的統計資料不會被閃回

當前的索引和從屬的物件會被維持

l閃回表操作:

系統表不能被閃回

不能跨越DDL操作

會被寫入警告日誌

產生撤銷和重做的資料

SQL> --閃回版本查詢

SQL> creat table versions_table

SP2-0734: 未知的命令開頭 "creat tabl..."- 忽略了剩餘的行。

SQL> create table versions_table

2 (vid number,

3 vname varchar(20));

表已建立。

SQL> insert into versions_tablevalues(1,'Tom');

已建立 1 行。

SQL> commit;

提交完成。

SQL> insert into versions_tablevalues(2,'Mary');

已建立 1 行。

SQL> commit;

提交完成。

SQL> insert into versions_tablevalues(3,'Mike');

已建立 1 行。

SQL> commit;

提交完成。

SQL> update versions_table setvname='Mary123' where vid=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from versions_table;

VID VNAME

1 Tom

2 Mary123

3 Mike

SQL> /*

SQL> 問題：1

SQL> 1. 如何獲取前面三個版本？

SQL> 2. 如何獲取該表過去的一個時間?

SQL> */

閃回版本查詢

閃回版本查詢，提供了一個審計行改變的查詢功能，透過它可以查詢到所有已經提交了的行記錄。其語法格式如下：

selectcolumn_name[,column_name,...]

from table_name

versionsbetween [SCN|TIMESTAMP] [expr|MINVALUE]

and [epxr|MAXVALUE] as of [SCN|TIMESTAMP]expr;

備註：

其中：column_name列名；table_name表名；between...and時間段；SCN系統改變號；TIMESTAMP時間戳；ASOF表示恢復單個版本;MAXVALUE最大值；MINVALUE最小值；expr指定一個值或者表示式。

SQL> --執行閃回版本查詢，獲取所有的版本資訊

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

2 from versions_table

3 versions between timestampminvalue and maxvalue

4 order by 1,4;

SQL> col VERSIONS_STARTTIME for a20

SQL> col VERSIONS_ENDTIME for a20

SQL> /

SQL> col vname for a8

SQL> /

SQL> col VERSIONS_STARTTIME for a30

SQL> col VERSIONS_ENDTIME for a30

SQL> /

SQL> set linesize 150

SQL> /

VID VNAME VVERSIONS_STARTTIME VERSIONS_ENDTIME

---------- -------- - ------------------------------

1Tom I 21-9月 -11 11.01.09 上午

2Mary I 21-9月 -11 11.01.24 上午 21-9月 -11 11.02.00 上午

2Mary123 U 21-9月 -11 11.02.00 上午

3Mike I 21-9月 -11 11.01.39 上午

SQL> /*

SQL> selectvid,vname,versions_operation,versions_starttime,versions_endtime

SQL> from versions_table

SQL> versions between timestamp minvalueand maxvalue

SQL> order by 1,4;

SQL> */

SQL> --透過閃回版本查詢，可以獲取某個表過去已經提交了的歷史版本資訊

SQL> host cls

SQL> --閃回事務查詢

SQL> create table transaction_table

2 (tid number,tnamevarchar(20));

表已建立。

SQL> --第一個事務

SQL> insert into transaction_tablevalues(1,'Tom');

已建立 1 行。

SQL> insert into transaction_table values(2,'Mary');

已建立 1 行。

SQL> insert into transaction_tablevalues(3,'Mike');

已建立 1 行。

SQL> commit;

提交完成。

SQL> --第二個事務

SQL> update transaction_table settname='Mary123' where tid=2;

已更新 1 行。

SQL> delete from transaction_table wheretid=3;

已刪除 1 行。

SQL> commit;

提交完成。

l閃回事務查詢實際上閃回版本查詢的一個擴充，透過它可以審計某個事務甚至撤銷一個已經提交的事務。

l實現閃回事務查詢，需要先了解flashback_transaction_query檢視，從該檢視中可以獲取事務的歷史操作記錄以及撤銷語句(UNDO_SQL)。

l使用閃回事務查詢，可以瞭解某個錶的歷史操作記錄，這個操作記錄對應一個撤銷SQL語句，如果想要撤銷這個操作，就可以執行這個SQL語句。

Flashback_transaction_query

名稱型別說明

------------------------------------------------------------------------------------------------

XID RAW(8) 事務編號

START_SCN NUMBER 事務的開始的系統改變號

START_TIMESTAMP DATE 事務的開始時間

COMMIT_SCN NUMBER 事務提交時的系統改變號

COMMIT_TIMESTAMP DATE 事務提交時的時間

LOGON_USER VARCHAR2(30) 對應的使用者

UNDO_CHANGE# NUMBER 撤銷操作對應的編號

OPERATION VARCHAR2(32) 操作

TABLE_NAME VARCHAR2(256) 表

TABLE_OWNER VARCHAR2(32) 所有者

ROW_ID VARCHAR2(19) 行號

UNDO_SQL VARCHAR2(4000) 撤銷事務的SQL語句

注意，要查詢flashback_transaction_query檢視的資訊，需要有select any transaction的許可權。

SQL> --如何撤銷第二個事務? ----閃回事務查詢

SQL> --授權select any transaction給scott

SQL> conn / as sysdba

已連線。

SQL> grant select any transaction toscott;

授權成功。

SQL> conn scott/tiger

已連線。

SQL> /*

SQL> 閃回事務查詢的步驟:1

SQL> 1. 透過閃回版本查詢獲取事務號(xid)

SQL> 2. 透過xid查詢Flashback_transaction_query，獲取undo_sql

SQL> */

SQL> select tid,tname,versions_operation,versions_starttime,versions_endtime,versions_xid

2 from transaction_table

3 versions between timestampminvalue and maxvalue

4 order by versions_xid;

TID TNAME V VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID

2 Mary123 U 21-9月 -11 11.13.24 上午 0400140020040000

3 Mike D 21-9月 -11 11.13.24 上午 0400140020040000

2 Mary I 21-9月 -11 11.12.39 上午 21-9月 -11 11.13.24 上午 0700250012040000

1 Tom I 21-9月 -11 11.12.39 上午 0700250012040000

3 Mike I 21-9月 -11 11.12.39 上午 21-9月 -11 11.13.24 上午 0700250012040000

SQL> -- 執行閃回事務查詢

SQL> select operation,undo_sql

2 fromFlashback_transaction_query

3 where xid='0400140020040000';

OPERATION

--------------------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

DELETE

insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');

UPDATE

update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';

BEGIN

SQL> update"SCOTT"."TRANSACTION_TABLE" set "TNAME" = 'Mary'where ROWID = 'AAANiBAAEAAAAG3AAB';

已更新 1 行。

SQL> insert into"SCOTT"."TRANSACTION_TABLE"("TID","TNAME")values ('3','Mike');

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from TRANSACTION_TABLE;

TID TNAME

---------- --------------------

1 Tom

2 Mary

3 Mike

SQL> host cls

SQL> spool off

Oracle 之閃回操作

SQL> */

SQL> select operation,undo_sql

相關文章

Oracle 之 閃回操作

SQL> */

SQL> select operation,undo_sql

相關文章

Oracle 之閃回操作