OCP课程51：管理II之使用闪回技术1

最新推荐文章于 2022-07-14 12:02:31 发布

cuishan5183

最新推荐文章于 2022-07-14 12:02:31 发布

阅读量235

点赞数

课程目标：

闪回技术
闪回查询
闪回版本查询
启用行迁移
闪回表
闪回事务查询
闪回事务

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

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?

此主题相关图片如下：