QUESTION NO:375
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
【题目示意】
此题考查有关于Flashback Transaction Query的相关知识。
【解析】
FLASHBACK_TRANSACTION_QUERY查看交易信息,包括事务ID,操作,操作的开始和结束SCNs,用户负责操作,和SQL代码显示的逻辑相反的操作
配置数据库Oracle倒叙事务查询功能,数据库管理员必须:确保运行Oracle数据库10.0版本兼容性,启用补充日志记录。
【实验】
1)查看FLASHBACK_TRANSACTION_QUERY的信息
SQL> desc FLASHBACK_TRANSACTION_QUERY
Name Null? Type
----------------------- -------- ----------------
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)
2)启动补充日志记录
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
3)FLASHBACK_TRANSACTION_QUERY查看交易信息
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1363528
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> insert into test values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from test where a=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1363597
SQL> SELECT XID,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL from FLASHBACK_TRANSACTION_QUERY Q WHERE Q.XID in(SELECT VERSIONS_XID FROM test VERSIONS BETWEEN SCN 1363528 and 1363597);
XID COMMIT_SCN COMMIT_TI OPERATION UNDO_SQL
-----------------------------------------------------------------------------------
0300080064030000 1363556 02-FEB-14 INSERT
delete from "SYS"."TEST" where ROWID = 'AAASikAABAAAWShAAC';
0300080064030000 1363556 02-FEB-14 INSERT
delete from "SYS"."TEST" where ROWID = 'AAASikAABAAAWShAAB';
0300080064030000 1363556 02-FEB-14 INSERT
delete from "SYS"."TEST" where ROWID = 'AAASikAABAAAWShAAA';
0300080064030000 1363556 02-FEB-14 BEGIN
0200090068030000 1363594 02-FEB-14 DELETE
insert into "SYS"."TEST"("A") values ('1');
0200090068030000 1363594 02-FEB-14 BEGIN
6 rows selected.
当数据库没有打开补充日志的时候,就没有undo_sql。
【小结】
在FLASHBACK_TRANSACTION_QUERY中有undo_sql是与sql代码逻辑相反的操作。因此选择C
【答案】 C
相关参考
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01005
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1006
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS611
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29435844/viewspace-1078494/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29435844/viewspace-1078494/