課程目標：

閃回技術
閃回查詢
閃回版本查詢
啟用行遷移
閃回表
閃回事務查詢
閃回事務

1、閃回技術

使用閃回技術處理邏輯錯誤，可以快速簡單恢復資料。使用閃回技術，可以追查定位人為錯誤影響的物件和行資料並進行修復。上圖列出的閃回技術，閃回資料庫使用閃回日誌，閃回刪除使用回收站，其他技術使用undo資料。

不是所有的閃回技術都會修改資料庫，有些技術只是查詢資料的其他版本，調查問題輔助恢復。例如閃回查詢可以：

確定資料庫修改的閃回操作執行解決問題的型別。
將這些查詢結果集作為插入、更新或刪除語句，以便可以輕鬆的修復錯誤資料。

閃回資料歸檔可以使用前面的邏輯閃回功能訪問很久以前的資料。

2、事務和UNDO

當一個事務開始，就會被分配一個UNOD段，如果資料修改，就會將原來的值複製到UNDO段，可以透過V$TRANSACTION檢視事務分配的是哪一個UNDO段。

UNDO段由例項自動建立，也由區組成，根據需要自動增長和收縮，類似一個環形儲存緩衝區。

事務用完UNDO段區的塊後，分配同一區的其他塊，如果區內沒有空閒的塊，則獲取段內下一區的塊，如果所有區都用完了，則開始迴圈使用第一個區或者請求分配給UNDO段的新區。

上圖中表的原始資料位於資料庫緩衝區快取，然後寫入到UNDO表空間。

注意：並行DML操作時，事務會使用超過一個UNDO段。

3、保證undo保留

為避免由於UNDO空間不足而導致事務失敗，UNDO預設是會覆蓋已經提交的事務的還沒有過期的UNDO資料。也就是說，事務優先於查詢。

可以透過設定保證保留時間改變以上的預設動作，即使事務由於UNDO空間不足而失敗，也要確保UNDO資料保留至設定的時間。也就是說，查詢優先於事務。

RETENTION GUARANTEE是一個表空間屬性，不是一個初始化引數。可以透過SQL或者EM進行修改。修改UNDO表空間保留保證的語法：

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

修改回去的語法：

SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

4、資料庫閃回準備

啟用閃回，需要：

一個有足夠空間的UNDO表空間，存放閃回操作所需要的資料。使用者更新資料越頻繁，所需空間越多。如果不能確定表空間大小，可以先設定UNDO表空間為自動擴充套件，透過V$UNDOSTAT檢視檢視一段時間UNDO塊資訊，並計算空間需求，然後再設定UNDO表空間為合適固定大小。
預設啟用Automatic Undo Management。
對於固定大小的UNDO表空間，則Oracle自動調整UNDO保留時間。
對於自動擴充套件的UNDO表空間，則透過UNDO_RETENTION參照指定UNDO保留最小時間，預設為900秒。

透過V$UNDOSTAT檢視的TUNED_UNDORETENTION欄位檢視事務提交後對應的undo資料保留的時間。設定UNDO_RETENTION引數不會保證沒有過期的資料不會被覆蓋。如果系統需要更多的UNDO空間，則會覆蓋最近產生的未過期的UNDO資料。

為UNDO表空間指定RETENTION GUARANTEE語句確保不會覆蓋沒有過期的UNDO資料。
授予閃回許可權給使用者、角色或者應用。

建立閃回資料歸檔，滿足長時間的保留要求。

例子：檢視V$UNDOSTAT檢視獲取UNDO資訊

SQL> SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,

2 TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,

3 UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON",

4 MAXQUERYLEN, TUNED_UNDORETENTION

5 FROM v$UNDOSTAT;

BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXCON MAXQUERYLEN TUNED_UNDORETENTION

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

01/29/2016 16:36:07 01/29/2016 16:37:30 2 0 0 0 563 1403

01/29/2016 16:26:07 01/29/2016 16:36:07 2 35 162 3 563 1403

01/29/2016 16:16:07 01/29/2016 16:26:07 2 23 272 1 1167 2007

01/29/2016 16:06:07 01/29/2016 16:16:07 2 32 146 3 567 1407

01/29/2016 15:56:07 01/29/2016 16:06:07 2 142 413 3 1169 2010

01/29/2016 15:46:07 01/29/2016 15:56:07 2 16 192 2 569 1409

01/29/2016 15:36:07 01/29/2016 15:46:07 2 34 171 4 1173 2013

01/29/2016 15:26:07 01/29/2016 15:36:07 2 28 165 1 573 1413

01/29/2016 15:16:07 01/29/2016 15:26:07 2 13 139 1 1175 2015

例子：檢視UNDO表空間剩餘大小

SQL> SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS';

Enter value for undotbs: UNDOTBS1

old 1: SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='&UNDOTBS'

new 1: SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='UNDOTBS1'

SUM(BYTES)

----------

97255424

例子：檢視當前UNDO表空間EXTENT的使用情況

SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

STATUS SUM(BYTES) COUNT(*)

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

UNEXPIRED 11534336 11

EXPIRED 10747904 29

例子：檢視當前事務的UNDO使用情況

SQL> SELECT XIDUSN, XIDSLOT, XIDSQN, USED_UBLK FROM V$TRANSACTION WHERE STATUS='ACTIVE';

XIDUSN XIDSLOT XIDSQN USED_UBLK

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

5 30 3187 1

5、使用閃回技術查詢資料

使用閃回技術可以查詢物件的歷史版本，查詢歷史資料以及分析效能改變。

閃回查詢：查詢指定時間點的資料。
閃回版本查詢：檢視行的所有版本。
閃回事務查詢：查詢事務所做的改變，可以使用其“undo”SQL命令回滾事務。

6、閃回查詢

使用SELECT語句中的AS OF子句，查詢指定時間的資料。AS OF子句可以使用timestamp或者SCN指定時間戳。

例子：閃回查詢

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mI:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE

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

2016-01-29 19:06:49

SQL> update employees set salary=10000 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

SQL> select employee_id,salary from employees as of timestamp to_timestamp('2016-01-29 19:06:49','yyyy-mm-dd hh24:mi:ss') where employee_id=200;

EMPLOYEE_ID SALARY

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

200 4400

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

4446226

SQL> update employees set salary=20000 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

SQL> select employee_id,salary from employees as of scn 4446226 where employee_id=200;

EMPLOYEE_ID SALARY

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

200 10000

7、閃回查詢：示例

如果發現資料錯誤，可以使用閃回查詢的結果回到以前的資料。

例子：使用閃回查詢結果修改資料

SQL> update employees set salary=(select salary from employees as of timestamp to_timestamp('2016-01-29 19:06:49','yyyy-mm-dd hh24:mi:ss') where employee_id=200) where employee_id=200;

1 row updated.

SQL> select employee_id,salary from employees where employee_id=200;

EMPLOYEE_ID SALARY

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

200 4400

SQL> commit;

Commit complete.

8、閃回版本查詢

閃回查詢是查詢某一個時間點的資料，閃回版本查詢使用VERSIONS子句查詢某一段時間行的所有版本。

閃回版本查詢的返回結果是行的改變歷史，且只返回已提交的行，包括刪除和重新插入的行版本。提供了一種審計錶行的方式，並可以獲取改變行的事務資訊。可以使用返回的事務識別符號，透過LogMiner執行日誌挖掘，或者進行閃回事務查詢。

其中VERSION_XID是一個偽列，返回對應行版本的事務識別符號。

SQL> select versions_xid,salary from employees

2 versions between scn 4446226 and 4447033

3 where employee_id=200;

VERSIONS_XID SALARY

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

06001100B60D0000 30000

08001800080D0000 4400

070007000B0C0000 20000

10000

9、閃回版本查詢：注意事項

VERSIONS子句不能查詢以下型別的表：

外部表（External tables）
臨時表（Temporary tables）
固定表（Fixed tables）

VERSIONS子句同樣不能查詢檢視，但檢視定義可以使用VERSIONS子句。

如果有DDL語句修改了表的結構，則SELECT語句中的VERSIONS子句不能跨該DDL語句顯示該錶行的版本。也就是說，閃回版本查詢會在表結構改變這個時間點停止查詢。

某些維護操作，如段收縮，可跨塊移動錶行。在這種情況下，版本查詢過濾掉了這種幻象版本，因為行資料仍然是相同的。

10、閃回表：概覽

使用閃回表，可以將表恢復到某個時間點而不需要進行point-in-time恢復操作。

線上進行閃回表操作，只回滾修改的表及其依賴的物件。

閃回表語句作為單個事務執行，要麼所有表閃回成功，要麼整個事務回滾。

可以使用閃回版本查詢和閃回事務查詢確定閃回的時間點。

11、閃回表

用閃回表可以恢復表到指定的時間點而不需要還原備份。使用此功能時，會恢復表資料及其相關的物件（索引、約束、觸發器等）。閃回表使用UNDO表空間的資料。可以使用閃回版本查詢、閃回事務查詢來確定適當的閃回時間。

閃回表為使用者提供了方便快捷的恢復意外修改資料的方式，不需要資料庫管理員參與。但必須授予使用者FLASHBACK TABLE或者FLASHBACK ANY TABLE系統許可權。此外，還必須授予使用者SELECT、INSERT、DELETE和ALTER的物件許可權。

也可以使用EM閃回表。

12、啟用行遷移

必須對需要閃回的表啟用行遷移，Oracle才能在表內移動行。

使用EM啟用行遷移的步驟：

（1）

（2）

（3）

13、執行閃回表

使用EM進行閃回表的步驟如下：

（1）

（2）

（3）

（4）

（5）

（6）

14、閃回表：注意事項

閃回表的注意事項：

整個閃回表語句是一個事務。要麼都執行要麼都不執行。
閃回表獲得表的排他DML鎖。
受影響物件的統計資訊不會被閃回。
維護現有的索引，但刪除的索引不會重建。對提交物化檢視的依賴也自動維護。
如果在閃回的過程中違反了任何約束，則閃回操作終止。
閃回表操作不能跨DDL（只改變表儲存屬性的DDL語句除外）。
不能閃回系統表，遠端表和固定表。

15、閃回事務查詢

閃回事務查詢是一個診斷工具，可以檢視在事務級別對資料庫所做的更改。使使用者能夠在資料庫中診斷問題並進行分析和稽核。

可以使用flashback_transaction_query檢視來確定所有必要的SQL語句，用於撤消指定的事務或者指定的時期內所做的更改。

SQL> select column_name,comments from dba_col_comments where table_name='FLASHBACK_TRANSACTION_QUERY';

COLUMN_NAME COMMENTS

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

XID Transaction identifier

START_SCN Transaction start SCN

START_TIMESTAMP Transaction start timestamp

COMMIT_SCN Transaction commit SCN

COMMIT_TIMESTAMP Transaction commit timestamp

LOGON_USER Logon user for transaction

UNDO_CHANGE# 1-based undo change number

OPERATION forward operation for this undo

TABLE_NAME table name to which this undo applies

TABLE_OWNER owner of table to which this undo applies

ROW_ID rowid to which this undo applies

COLUMN_NAME COMMENTS

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

UNDO_SQL SQL corresponding to this undo

12 rows selected.

SQL> create table emp as select * from employees where 1=0;

Table created.

SQL> insert into emp select * from employees where employee_id=100;

1 row created.

SQL> commit;

Commit complete.

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_name='EMP';

XID START_SCN COMMIT_SCN ROW_ID OPERATION UNDO_SQL

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

010020005C0C0000 4542902 4542905 AAAV9xAAEAAAAI/AAA INSERT delete from "HR"."EMP" where ROWID = 'AAAV9xAAEAAAAI/AAA';

16、使用EM進行閃回事務查詢

17、閃回事務查詢：注意事項

在資料庫中，DDL操作只不過是一系列的空間管理操作和修改資料字典。對DDL的閃回事務查詢顯示了資料字典的變化。

如果從資料庫中刪除表，閃回事務查詢顯示物件編號而不會顯示錶名。

如果從資料庫中刪除使用者，閃回事務查詢顯示使用者ID而不是使用者名稱。

注意：當某個事務沒有足夠的UNDO資料，則FLASHBACK_TRANSACTION_QUERY的OPERATION欄位的值為UNKNOWN。

18、閃回事務

使用閃回事務，可以迴轉事務及相關事務，相當於建立了一個補償事務用於迴轉不必要的更改。

可以使用EM中的閃回事務功能或者PL/SQL包。

19、前提條件

使用閃回事務查詢，必須啟用補充日誌並授予正確的許可權。例如，在HR模式的HR使用者想使用閃回事務查詢REGIONS表，SYSDBA需要在SQL*Plus中執行如下命令：

alter database add supplemental log data;

alter database add supplemental log data (primary key) columns;

grant execute on dbms_flashback to hr;

grant select any transaction to hr;

例子：啟用閃回事務查詢

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database add supplemental log data(primary key) columns;

Database altered.

SQL> grant execute on dbms_flashback to hr;

Grant succeeded.

SQL> grant select any transaction to hr;

Grant succeeded.

20、閃回一個事務

安全許可權：

閃回事務，或者說建立一個補償事務，需要有相關表的SELECT，FLASHBACK和DML許可權。

使用條件：

事務迴轉不支援衝突的DDL語句。
事務迴轉繼承日誌型別支援日誌分析。

建議：

當發現需要進行事務迴轉的時候，越早進行效能越好，否則大量重做日誌和事務會導致較慢的事務迴轉操作。

為事務迴轉提供一個事務名字以方便後續的審計。如果不提供事務名，則會自動生成。

可以使用EM或者命令列進行閃回事務，EM使用閃回事務嚮導，呼叫DBMS_FLASHBACK.TRANSACTION_BACKOUT過程，並使用NOCASCADE選項。如果成功執行，則表明該事務沒有依賴關係。

21、步驟

假如發生瞭如下事務：

SQL> conn hr/hr

Connected.

SQL> insert into regions values(5,'Pole');

1 row created.

SQL> commit;

Commit complete.

SQL> update regions set region_name='Poles' where region_id=5;

1 row updated.

SQL> update regions set region_name='North and South Poles' where region_id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> insert into countries values('TT','Test Country',5);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba

Connected.

SQL> alter system archive log current;

System altered.

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_name='REGIONS';

XID START_SCN COMMIT_SCN ROW_ID OPERATION UNDO_SQL

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

010021004C0C0000 4497006 4497014 AAAVS5AAFAAAACLAAA INSERT delete from "HR"."REGIONS" where ROWID = 'AAAVS5AAFAAAACLAAA';

05001600200D0000 4497361 4497376 AAAVS5AAFAAAACLAAA UPDATE update "HR"."REGIONS" set "REGION_NAME" = 'Poles' where ROWID = 'AAAVS5AAFAAAACLAAA';

05001600200D0000 4497361 4497376 AAAVS5AAFAAAACLAAA UPDATE update "HR"."REGIONS" set "REGION_NAME" = 'Pole' where ROWID = 'AAAVS5AAFAAAACLAAA';

22、閃回事務嚮導

例子：使用閃回事務嚮導進行閃回事務

SQL> select * from regions where region_id=5;

REGION_ID REGION_NAME

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

5 Pole

例子：使用PL/SQL進行閃回事務

SQL> create table emp as select * from employees where 1=0;

Table created.

SQL> insert into emp select * from employees where employee_id=100;

1 row created.

SQL> commit;

Commit complete.

SQL> select xid, start_scn, commit_scn, row_id, operation,undo_sql from flashback_transaction_query where table_name='EMP';

XID START_SCN COMMIT_SCN ROW_ID OPERATION UNDO_SQL

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

010020005C0C0000 4542902 4542905 AAAV9xAAEAAAAI/AAA INSERT delete from "HR"."EMP" where ROWID = 'AAAV9xAAEAAAAI/AAA';

SQL> select * from emp;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

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

100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 10000 90

SQL> conn / as sysdba

Connected.

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('010020005C0C0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from hr.emp;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select * from hr.emp;

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

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

100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 10000 90

23、選擇其他的回退選項

事務間存在的依賴關係主要有以下三種：

Write-after-write dependency：

Transaction 1 changes a row of a table, and later transaction 2 changes the same row.（事務1更改了表的行，後續的事務2又更改了相同的行）

Primary key dependency：

A table has a primary key constraint on column c. In a row of the table, column c has the value v. Transaction 1 deletes that row, and later transaction 2 inserts a row into the same table, assigning the value v to column c.（表的C列為主鍵約束，表的某一行的C列的值為V，事務1刪除了該行，後續的事務2插入了一行，且C列的值為V，即在一張擁有主鍵的表中TX1首先刪除了一行，之後TX2又插入了具有相同主鍵值的另一行。）

Foreign key dependency：

In table b, column b1 has a foreign key constraint on column a1 of table a. Transaction 1 changes a value in a1, and later transaction 2 changes a value in b1.（表b中列b1有一個外來鍵約束參考表a的列a1，事務1更改了a1的一個值，後續的事務2修改了b1的一個值，即由於TX1的修改（insert或update）而產生了新的可被外來鍵參考的欄位值，之後TX2修改（insert或update）外來鍵欄位時利用了TX1所產生的欄位值。）

24、選擇其他的回退選項

如果在EM的閃回事務嚮導中使用預設的NOCASCADE選項執行閃回事務失敗，表明存在依賴的事務，需要更改恢復選項，oracle提供了四種閃回選項：

NOCASCADE（無級聯，預設）：若檢測到Transaction之間存在依賴關係，則無法進行回退。

NONCONFLICT_ONLY（僅限無衝突）：僅回退Transaction裡不存在依賴關係的SQL，保證事務的一致性，會破壞事務的完整性。

NOCASCADE_FORCE（強制無級聯）：若Transaction間僅存在”Write-after-write dependency”型別的依賴關係，則可以實施強行回退；若遇到除”Write-after-write dependency”以外的其它型別的依賴關係，則無法進行回退。

CASCADE（級聯）：對存在依賴關係的Transaction實施連帶回退。

例子：對前面的三種依賴關係應用這四種閃回選項

（1）Write-after-write dependency

先構造表和資料

SQL> conn / as sysdba

Connected.

SQL> create table emp as select employee_id,department_id from hr.employees where 1=0;

Table created.

SQL> create table dept as select department_id,department_name from hr.departments where 1=0;

Table created.

SQL> insert into emp values(1,1);

1 row created.

SQL> commit;

Commit complete.

以上一個插入為事務1

SQL> insert into emp values(2,2);

1 row created.

SQL> insert into emp values(3,4);

1 row created.

SQL> commit;

Commit complete.

以上2個插入為事務2

SQL> update emp set department_id=3 where department_id=4;

1 row updated.

SQL> commit;

Commit complete.

以上更新為事務3

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

3 3

SQL> col versions_starttime format a25;

SQL> col versions_endtime format a25;

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from emp versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPLOYEE_ID DEPARTMENT_ID

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

31-JAN-16 06.25.56 PM 050017002E0D0000 U 3 3

31-JAN-16 06.25.31 PM 31-JAN-16 06.25.56 PM 09000A00890D0000 I 3 4

31-JAN-16 06.25.31 PM 09000A00890D0000 I 2 2

31-JAN-16 06.24.28 PM 02001F003B0D0000 I 1 1

使用NOCASCADE選項，對事務2進行閃回，由於後續的事務3對事務2的資料進行了修改，故不能執行成功。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

表的內容沒有變化：

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

3 3

使用NONCONFLICT_ONLY選項，對事務2進行閃回。事務2有兩條insert語句，事務3的update語句依賴於事務2的第2條insert語句，所以僅回退事務2裡的1條insert語句。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

回滾閃回事務。

SQL> rollback;

Rollback complete.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

3 3

使用NOCASCADE_FORCE選項，對事務2進行閃回。事務2的兩條insert語句回退了，事務3裡的update雖然是基於事務2裡第一條insert的結果，update的結果還是被保留下來了，可以看出NOCASCADE_FORCE在處理具有"Write-after-write dependency"依賴關係的記錄時不會考慮記錄間的依賴關係僅回退指定事務的操作。（此處結果與NONCONFLICT_ONLY，是否有問題）

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

SQL> rollback;

Rollback complete.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

3 3

使用CASCADE選項，對事務2進行閃回。要回退的是事務2，因為事務3依賴於事務2，所以事務3隨著事務2一起被回退，並且回退的順序應該是先回退事務3再回退事務2。可見CASCADE會把與要回退的事務存在依賴關係的事務一起回退。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('09000A00890D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

SQL> rollback;

Rollback complete.

SQL> select * from emp;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

3 3

（2）Primary key dependency

SQL> alter database add supplemental log data(primary key) columns;

Database altered.

必須開啟primary key supplemental logging，否則執行dbms_flashback.transaction_backout時會出現ORA-55511: Flashback Transaction experienced error in executing undo SQL。

構造表和資料

SQL> create table emp1(employee_id number primary key,department_id number);

Table created.

SQL> insert into emp1 values(1,1);

1 row created.

SQL> insert into emp1 values(2,2);

1 row created.

SQL> commit;

Commit complete.

以上2個insert語句為事務1。

SQL> insert into emp1 values(3,3);

1 row created.

SQL> delete emp1 where employee_id=2;

1 row deleted.

SQL> commit;

Commit complete.

以上的insert和delete為事務2。

SQL> insert into emp1 values(2,4);

1 row created.

SQL> commit;

Commit complete.

以上的insert為事務3。

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

2 4

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from emp1 versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V EMPLOYEE_ID DEPARTMENT_ID

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

31-JAN-16 08.31.08 PM 0A0007008D0C0000 I 2 4

31-JAN-16 08.30.47 PM 03001D005B0D0000 D 2 2

31-JAN-16 08.30.47 PM 03001D005B0D0000 I 3 3

31-JAN-16 08.29.14 PM 31-JAN-16 08.30.47 PM 01000000700C0000 I 2 2

31-JAN-16 08.29.14 PM 01000000700C0000 I 1 1

使用NOCASCADE選項，對事務2進行閃回，由於後續的事務3對事務2的資料進行了修改，故不能執行成功。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

2 4

使用NONCONFLICT_ONLY選項，對事務2進行閃回。事務3裡的insert依賴於事務2裡的delete，所以僅回退了事務2裡的insert，NONCONFLICT_ONLY在flashback時會避開有依賴關係的行。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 4

SQL> rollback;

Rollback complete.

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

2 4

使用NOCASCADE_FORCE選項，對事務2進行閃回。NOCASCADE_FORCE僅能回退存在"Write-after-write dependency"依賴關係的事務，如果依賴關係來自於primary key或者foreign key則無法實現回退。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-55511: Flashback Transaction experienced error in executing undo SQL

ORA-00001: unique constraint (ORA-00001: unique constraint (SYS.SYS_C0011795) violated

.) violated

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

2 4

使用CASCADE選項，對事務2進行閃回。要回退的是事務2，但事務3依賴於事務2，Cascade能夠將有級聯關係的事務一起回退掉。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('03001D005B0D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

2 2

SQL> rollback;

Rollback complete.

SQL> select * from emp1;

EMPLOYEE_ID DEPARTMENT_ID

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

1 1

3 3

2 4

（3）Foreign key dependency

SQL> alter database add supplemental log data(foreign key) columns;

Database altered.

必須開啟foreign key supplemental logging才能追蹤foreign key的依賴關係。

構造表和資料

SQL> create table dept2(department_id number primary key,department_name varchar2(20));

Table created.

SQL> create table emp2(employee_id number,department_id number references dept2(department_id));

Table created.

SQL> insert into dept2 values(1,'a');

1 row created.

SQL> commit;

Commit complete.

以上1個insert為事務1。

SQL> insert into dept2 values(2,'b');

1 row created.

SQL> insert into dept2 values(3,'c');

1 row created.

SQL> commit;

Commit complete.

以上2個insert為事務2。

SQL> insert into emp2 values(200,2);

1 row created.

SQL> commit;

Commit complete.

以上1個insert為事務3。

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from dept2 versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPARTMENT_ID DEPARTMENT_NAME

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

31-JAN-16 09.25.27 PM 06000700170E0000 I 3 c

31-JAN-16 09.25.27 PM 06000700170E0000 I 2 b

31-JAN-16 09.25.12 PM 07001400660C0000 I 1 a

使用NOCASCADE選項，對事務2進行閃回，由於後續的事務3對事務2的依賴，故不能執行成功。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('06000700170E0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-55504: Transaction conflicts in NOCASCADE mode

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

SQL> select * from dept2;

DEPARTMENT_ID DEPARTMENT_NAME

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

1 a

2 b

3 c

使用NONCONFLICT_ONLY選項，對事務2進行閃回。僅回退了事務2裡的第2條insert，事務2裡的第1條insert和事務3保持原狀。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('0A001F00930C0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nonconflict_only);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from dept2;

DEPARTMENT_ID DEPARTMENT_NAME

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

1 a

2 b

SQL> select * from emp2;

EMPLOYEE_ID DEPARTMENT_ID

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

200 2

SQL> rollback;

Rollback complete.

SQL> select * from dept2;

DEPARTMENT_ID DEPARTMENT_NAME

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

1 a

2 b

3 c

SQL> select * from emp2;

EMPLOYEE_ID DEPARTMENT_ID

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

200 2

使用NOCASCADE_FORCE選項，對事務2進行閃回。NOCASCADE_FORCE僅能回退存在"Write-after-write dependency"依賴關係的事務，如果依賴關係來自於primary key或者foreign key則無法實現回退。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('0A001F00930C0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [ktftbProcessKGL_4], [7901], [7842], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

但是出現上面這個錯誤，網上沒有這個錯誤的資料，MOS裡面也沒有搜尋到（也有可能是我的搜尋方法不對），有知道的朋友麻煩分享一下），只能重新構造表和資料。

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,t.* from dept2 versions between scn minvalue and maxvalue t;

VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V DEPARTMENT_ID DEPARTMENT_NAME

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

31-JAN-16 09.55.36 PM 02000B00500D0000 I 3 c

31-JAN-16 09.55.36 PM 02000B00500D0000 I 2 b

31-JAN-16 09.55.30 PM 01000B00780C0000 I 1 a

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('02000B00500D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.nocascade_force);

5 end;

6 /

declare

*

ERROR at line 1:

ORA-55511: Flashback Transaction experienced error in executing undo SQL

ORA-02292: integrity constraint (ORA-02292: integrity constraint (SYS.SYS_C0011807) violated - child record found

.) violated - child record found

ORA-06512: at "SYS.DBMS_FLASHBACK", line 37

ORA-06512: at "SYS.DBMS_FLASHBACK", line 70

ORA-06512: at line 4

使用CASCADE選項，對事務2進行閃回。事務3依賴於事務2裡的第一條insert，事務2、事務3全部回退。

SQL> declare

2 v_xid sys.xid_array:=sys.xid_array(hextoraw('02001B00510D0000'));

3 begin

4 sys.dbms_flashback.transaction_backout(1,v_xid,dbms_flashback.cascade);

5 end;

6 /

PL/SQL procedure successfully completed.

SQL> select * from dept2;

DEPARTMENT_ID DEPARTMENT_NAME

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

1 a

SQL> select * from emp2;

no rows selected

四種閃回選項在各種依賴關係下是否能夠成功閃回的總結：

NOCASCADE

NOCASCADE_FORCE

CASCADE

NONCONFLICT_ONLY

Write-after-write dependency

否

是

Primary key dependency

否

是

Foreign key dependency

否

是

25、不使用EM的後續步驟

選擇回退選項後，會在DBA_FLASHBACK_TXN_STATE和DBA_FLASHBACK_TXN_REPORT檢視中生成相關性報告。

檢查顯示了已回退的所有事務處理的相關性報告。
提交更改使其成為永久更改。
回退放棄更改。

DBA_FLASHBACK_TXN_STATE檢視包含事務處理的當前狀態：該事務處理在系統中處於活動狀態還是已被有效回退。

DBA_FLASHBACK_TXN_REPORT檢視可提供關於資料庫中已提交的所有迴轉事務處理的詳細資訊。此檢視中的每一行都與一個迴轉事務處理相關聯。

26、相關習題

（1）View the following SQL statements:

Transaction T1 INSERT INTO hr.regions VALUES (5,'Pole');COMMIT;

Transaction T2 UPDATE hr.regions SET regi WHERE region_id = 5; COMMIT;

Transaction T3 UPDATE hr.regions SET regi WHERE region_id = 5;

You want to back out transaction T2. Which option would you use?

A.It is possible, but transaction T3 also backs out.

B.It is possible with the NOCASCADE_FORCE option.

C.It is possible with the NONCONFLICT_ONLY option.

D.It is not possible because it has conflicts with transaction T3.

答案：B

（2）You discover that your Recycle Bin contains two tables with the same name, MY_TABLE. You also have a table named MY_TABLE in your schema. You execute the following statement:

FLASHBACK TABLE my_table TO BEFORE DROP RENAME TO my_table2;

What will be the result of executing this statement?

A.One of the tables is recovered from the Recycle Bin using a First In First Out (FIFO) approach.

B.One of the tables is recovered from the Recycle Bin using a Last In First Out (LIFO) approach.

C.Both the tables are recovered from the Recycle Bin with one table renamed to MY_TABLE2 and the other to a system-generated name.

D.None of the tables are recovered from the Recycle Bin, and the statement returns an error.

答案：B

（3）Which method would you use to undo the changes made by a particular transaction without affecting the changes made by other transactions?

A.point-in-time recovery

B.execute the ROLLBACK command with transaction number

C.flashback the database to before the transaction was committed

D.determine all the necessary undo SQL statements from FLASHBACK_TRANSACTION_QUERY and use them for recovery

答案：D

（4）On which two database objects can the VERSIONS clause of the Flashback Versions Query be used? (Choose two.)

A.fixed tables

B.heap tables

C.external tables

D.temporary tables

E.Index-Organized Tables (IOTs)

答案：BE

（5）The EMP table exists in your schema. You want to execute the following query:

SELECT ename, sal FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '6' MINUTE) WHERE ename = 'ALLEN';

What are the minimum requirements for the statement to execute successfully? (Choose all that apply)

A.ARCHIVELOG mode must be enabled

B.Row Movement must be enabled for the table

C.FLASHBACK must be set to ON for the database

D.The UNDO_MANAGEMENT parameter must be set to AUTO

E.The UNDO_RETENTION parameter must be set appropriately

答案：DE

（6）Which three actions are required to configure the Flashback Database? (Choose three.)

A.set Flash Recovery Area

B.enable Flashback logging

C.create FLASHBACK tablespace

D.start the database in the ARCHIVELOG mode

E.start the database in the NOARCHIVELOG mode

答案：ABD

（7）The RECYCLEBIN parameter is set to ON for your database. You drop a table, PRODUCTS, from the SCOTT schema.

Which two statements are true regarding the outcome of this action? (Choose two)

A.All the related indexes and views are automatically dropped

B.The flashback drop feature can recover only the table structure

C.Only the related indexes are dropped whereas views are invalidated

D.The flashback drop feature can recover both the table structure and its data

答案：CD

（8）Which are the prerequisites for performing flashback transactions on your database? (Choose all that apply.)

A. Undo retention guarantee for the database must be configured.

B. Supplemental log must be enabled for the primary key.

C. Supplemental log must be enabled.

D. Execute permission on the DBMS_FLASHBACK package must be granted to the user.

答案：BCD

（9）Which two statements regarding the Flashback Table feature are correct? (Choose two.)

A. Flashback Table can be performed on system tables.

B. Flashback Table operation does not shrink the segments.

C. Flashback Table uses log mining to extract SQL_REDO and SQL_UNDO statements.

D. Flashback Table operation acquires exclusive data manipulation language (DML) locks.

答案：BD

（10）You plan to use Flashback Drop feature to recover a dropped table SALES_EMP. No other table with the same name exists in the schema.

You query RECYCLEBIN and find multiple entries for the SALES_EMP table as follows: You then issue the following statement to recover the table:

SQL> FLASHBACK TABLE sales_emp TO BEFORE DROP;

What would be the outcome of the precedent statement?

此主題相關圖片如下：

A. It retrieves the latest version of the table from the recycle bin

B. It retrieves the oldest version of the table from the recycle bin

C. It retrieves the version of the table for which undo information is available

D. It returns an error because the table name is not specified as per the names in the OBJECT_NAME column

答案：A

（11）Which of the following Oracle features utilize the undo tablespace? (Choose all that apply)

A. Flashback Query

B. Flashback Drop

C. Flashback Table

D. Flashback Database

E. Transaction Processing

F. Recycle Bin

答案：ACE

（12）Which of the following statements are true regarding the Recycle Bin? (Choose all that apply.)

A. The Recycle Bin is a physical storage area for dropped objects.

B. The Recycle Bin is a logical container for dropped objects.

C. The Recycle Bin stores the results of a Flashback Drop operation.

D. The objects in the Recycle Bin are stored in the tablespace in which they were created.

答案：BD

（13）Over the course of a day, a department performed multiple DML statements (inserts, updates,deletes) on multiple rows of data in multiple tables. The manager would like a report showing the time, table name, and DML type for all changes that were made. Which Flashback technology would be the best choice to produce the list?

A. Flashback Drop

B. Flashback Query

C. Flashback Transaction Query

D. Flashback Versions Query

E. Flashback Table

答案：C

（14）A user named Arren is executing this query:

select table_name, operation, undo_sql

from flashback_transaction_query t,

(select versions_xid as xid

from employees versions between scn minvalue

and maxvalue where employee_id = 123) e

where t.xid = e.xid;

When the query runs, he receives an ORA-01031: insufficient privileges error. Since the user owns the employees table, you know that it is not the problem. Which of the following SQL statements will correct this problem?

A. GRANT SELECT ANY TRANSACTION TO ARREN;

B. GRANT SELECT ON FLASHBACK_TRANSACTION_QUERY TO ARREN;

C. GRANT SELECT_ANY_TRANSACTION TO ARREN;

D. GRANT FLASHBACK TO ARREN;

E. GRANT SELECT ANY VIEW TO ARREN;

答案：A

（15）AUM has been retaining about 15 minutes worth of undo. You want to double the retention period, but not at the expense of new transactions failing. You decide to alter the system to set the parameter UNDO_RETENTION=18000. However, AUM still retains only about 15 minutes worth of undo. What is the problem? (Choose the best answer.)

A. You need to alter the undo tablespace to add the RETENTION GUARANTEE setting.

B. You need to increase the size of the undo tablespace.

C. The undo tablespace is not set to auto-extend.

D. You need to alter the Recycle Bin to add the RETENTION GUARANTEE setting.

答案：C

（16）In order to perform Flashback Transaction Query operations, which of these steps are required?

(Choose all that apply.)

A. Ensure that database is running with version 10.1 compatibility.

B. Enable Flashback Logging.

C. Enable Supplemental Logging.

D. Ensure that the database is running with version 10.0 compatibility.

E. Ensure that the database is in ARCHIVELOG mode

答案：CD

（17）Users notify you that their application is failing every time they try to add new records. Because of poor application design, the actual ORA error message is unavailable. What might be the problem?

(Choose the best answers.)

A. The application user has exceeded their undo quota.

B. The FLASHBACK GUARANTEE option is set on the undo tablespace.

C. The table is currently being queried by a Flashback Transaction Query operation.

D. The table is currently being queried by a Flashback Versions Query operation.

E. The RETENTION GUARANTEE option is set on the undo tablespace.

答案：AE

（18）Which of the following statements best describes Flashback Versions Query?

A. Flashback Versions Query is used to make changes to multiple versions of data that existed between two points in time.

B. Flashback Versions Query is used to view all version changes on rows that existed between the time the query was executed and a point in time in the past.

C. Flashback Versions Query is used to view version changes and the SQL to undo those changes on rows that existed between two points in time.

D. Flashback Versions Query is used to view all version changes on rows that existed between two points in time.

答案：D

（19）Which pseudocolumn could you use to identify a unique row in a Flashback Versions Query?

A. XID

B. VERSIONS_PK

C. VERSIONS_XID

D. VERSIONS_UNIQUE

答案：C

（20）Which of the following can be used in conjunction with a Flashback Versions Query to filter the results? (Choose all that apply.)

A. A range of SCN values

B. A list of SCN values

C. A starting and ending timestamp

D. Minimum and maximum sequence values

E. A list of sequence values

答案：AC

（21）At the request of a user, you issue the following command to restore a dropped table: flashback table "BIN$F2JFfMq8Q5unbC0ceE9eJg==$0" to before drop; Later, the user notifies you that the data in the table seems to be very old and out of date. What might be the problem?

A. Because a proper range of SCNs was not specified, the wrong data was restored.

B. A proper range of timestamps was not specified, so the wrong data was restored.

C. A previous Flashback Drop operation had been performed, resulting in multiple versions of the table being stored in the Recycle Bin.

D. Either option A or B could be correct. Not enough information was provided to determine which.

E. None of the above.

答案：C

（22）Which of the following statements is true regarding the VERSIONS BETWEEN clause?

A. The VERSIONS BETWEEN clause may be used in DML statements.

B. The VERSIONS BETWEEN clause may be used in DDL statements.

C. The VERSIONS BETWEEN clause may not be used to query past DDL changes to tables.

D. The VERSIONS BETWEEN clause may not be used to query past DML statements to tables.

答案：C

（23）Which of the following statements is true regarding implementing a Flashback Table recovery?

A. An SCN is never used to perform a Flashback Table recovery.

B. If a significant number of changes have been made to the table, row movement must be enabled.

C. The tablespace must be offline before performing a Flashback Table recovery.

D. Flashback Table recovery is completely dependent on the availability of undo data in the undo tablespace.

答案：D

（24）You have just performed a FLASHBACK TABLE operation using the following command:

flashback table employees to scn 123456;

The employees table has triggers associated with it. Which of the following statements is true regarding the state of the triggers during the Flashback Table operation?

A. All the triggers are disabled.

B. All the triggers are enabled by default.

C. Enabled triggers remain enabled and disabled triggers remain disabled.

D. Triggers are deleted when a Flashback Table operation is performed.

答案：A

（25）Which method could be utilized to identify both DML operations and the SQL statements needed to undo those operations for a specific schema owner? (Choose all that apply.)

A. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.

B. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and UNDO_SQL.Limit rows by START_SCN and TABLE_OWNER.

C. Query FLASHBACK_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and UNDO_SQL.Limit rows by START_TIMESTAMP and TABLE_OWNER.

D. Query DBA_TRANSACTION_QUERY for TABLE_NAME, OPERATION, and UNDO_SQL. Limit rows by START_SCN and TABLE_OWNER.

答案：BC

（26）For which two database objects can the VERSIONS clause of the Flashback Versions Query be used?(Chooset wo.)
A.views

B. fixed tables

C.heap tables

D.external tables

E.temporary tables
F .index-organized tables (IOT)

答案：CF

（27）A user performs an update on a table. Shortly after committing the transaction, they realize that they had an error in their WHERE clause causing the wrong rows to be updated. Which Flashback option would allow you to undo this transaction and restore the table to its previous state?

A. Flashback Drop

B. Flashback Query

C. Flashback Versions Query

D. Flashback Transaction Query

E. Flashback Table

答案：E

（28）A developer calls and reports that he accidentally dropped an important lookup table from a production database. He needs the table to be recovered. What action would you take?

A. Initiate an incomplete recovery operation using RMAN.

B. Copy the table from a development database.

C. Advise the user to rekey the data.

D. Perform a Flashback Drop operation.

E. Perform a Flashback Recovery operation.

答案：D

（29）Examine the following commands and their output:

SQL> SELECT ename, sal FROM emp WHERE ename='JAMES';

ENAME SAL

JAMES 1050

SQL> UPDATE emp SET sal=sal+sal*1.2 WHERE ename='JAMES';

1 row updated.

SQL> SELECT ename, sal FROM emp WHERE ename='JAMES';

ENAME SAL

JAMES 2310

View the exhibit and examine the Flashback Version Query that was executed after the preceding commands.

What could be the possible cause for the query not displaying any row?

Exhibit:

此主題相關圖片如下：

A. Flashback logging is not enabled for the database.

B. The changes made to the table are not committed.

C. Supplemental logging is not enabled for the database.

D. The database is not configured in ARCHIVELOG mode.

答案：B

（30）Before a Flashback Table operation, you execute the following command:

ALTER TABLE employees ENABLE ROW MOVEMENT;

Why would you need this to be executed?

A. Because row IDs may change during the flashback operation

B. Because the object number changes after the flashback operation

C. Because the rows are retrieved from the recycle bin during the flashback operation

D. Because the table is moved forward and back to a temporary during the flashback operation

答案：A

（31）The EMP table has some discrepancy in data entry with a particular employee ID. You execute the query as shown in the Exhibit to retrieve all versions of the row that exist between two SCNs.

View the Exhibit.

Which two statements about the results of the query shown in the Exhibit are correct? (Choosetwo.)

Exhibit:

此主題相關圖片如下：

A. The LAST_SCN value in the first row is NULL, which means that the versions of the row still exist at SCN 6636300.

B. The LAST_SCN value in the second row in NULL, which means that the version of the row still exists at SCN 6636300.

C. The LAST_SCN value in the third row is 6636280, which means that the version of row exists above SCN 6636280.

D. The LAST_SCN value in the second row is NULL, which means that the version of the row no longer exists because it was deleted.

答案：AD

（32）Note the following statements that use flashback technology:

1. FLASHBACK TABLETO SCN ;

2. SELECT * FROM

AS OF SCN 123456;

3. FLASHBACK TABLE

TO BEFORE DROP;

4. FLASHBACK DATABASE TO TIMESTAMP ;

5. SELECT * FROM

VERSIONS AS OF SCN 123456 AND 123999;

Which of these statements will be dependent on the availability of relevant undo data in the undo segment?

A. 1, 2, and 5

B. 1, 3, and 4

C. 2, 3, 4, and 5

D. 1, 2, 3, 4, and 5

答案：A

（33）You are working in an online transaction processing (OLTP) environment. You use the FLASHBACK TABLE command to flash back the CUSTOMERS table. Before executing the FLASHBACK TABLE command, the system change number (SCN) was 663571. After flashing back the CUSTOMERS table, you realize that the table is not in the correct state. Now, you need to reverse the effects of the FLASHBACK TABLE command. Which is the fastest and the most efficient option to reverse the effects of the FLASHBACK TABLE command?

A. Restore the backup control file and open the database with RESETLOGS option.

B. Perform point-in-time recovery because flashback cannot be performed again on this table

C. Execute the FLASHBACK DATABASE statement to retrieve the CUSTOMERS table as it was at SCN 663571

D. Execute another FLASHBACK TABLE statement to retrieve the CUSTOMERS table as it was at SCN 663571

答案：D

（34）User SCOTT wants to back out the transactions on the REGIONS table in his schema. As a DBA, which commands must you execute to enable SCOTT to flash back the transactions? (Choose four.)

A. ALTER DATABASE FLASHBACK ON;

B. GRANT SELECT any transaction TO scott;

C. GRANT EXECUTE ON dbms_flashback TO scott;

D. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

E. ALTER TABLESPACE undots1 RETENTION GUARANTEE;

F. ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

答案：BCDF

（35）What are the prerequisites for performing flashback transactions? (Choose all that apply)

A.Supplemental log must be enabled

B.Supplemental log must be enabled for the primary key

C.Undo retention guarantee for the database must be configured

D.”EXECUTE “ permission on the DBMS_FLASHBACK package must be granted to the user

答案：ABD

（36）You execute the following FLASHBACK TABLE command:

Which two statements are correct?(Choose two.)

A.The EMP table that was dropped by mistake earlier is restored.

B.The FLASHBACK TABLE statement is executed as a single transaction.

C.The FLASHBACK TABLE statement does not maintain existing indexes on the EMP table.

D.The changes made to the EMP table since the specified time are undone if no constraint is violated during flashback.

答案：BD

（37）View the Exhibit and examine the data manipulation language (DML) operations that you performed on the NEWEMP table. Note that the first two updated are not listed by the Flashback Versions Query.

What could be the reason?

此主題相關圖片如下：