QUESTION NO: 387
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.
【题目示意】
此题考查有关于undo的相关知识。
【解析】
FLASHBACK_TRANSACTION_QUERY可以显示数据库中闪回事务的所有的信息。数据库必须开启数据库的补充日志。
FLASHBACK_TRANSACTION_QUERY
Column | Datatype | NULL | Description |
XID | RAW(8) |
| Transaction identifier |
START_SCN | NUMBER |
| Transaction start system change number (SCN) |
START_TIMESTAMP | DATE |
| Transaction start timestamp |
COMMIT_SCN | NUMBER |
| Transaction commit system change number; NULL for active transactions |
COMMIT_TIMESTAMP | DATE |
| Transaction commit timestamp; NULL for active transactions |
LOGON_USER | VARCHAR2(30) |
| Logon user for the transaction |
UNDO_CHANGE# | NUMBER |
| Undo system change number (1 or higher) |
OPERATION | VARCHAR2(32) |
| Forward-going DML operation performed by the transaction: D - Delete I - Insert U - Update B UNKNOWN |
TABLE_NAME | VARCHAR2(256) |
| Name of the table to which the DML applies |
TABLE_OWNER | VARCHAR2(32) |
| Owner of the table to which the DML applies |
ROW_ID | VARCHAR2(19) |
| Rowid of the row that was modified by the DML |
UNDO_SQL | VARCHAR2(4000) |
| SQL to undo the DML indicated by OPERATION |
【实验】
1)FLASHBACK_TRANSACTION_QUERY查看交易信息
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1426102
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(12);
1 row created.
SQL> insert into test values(13);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> delete from test where a=12;
1 row deleted.
SQL> commit;
Commit complete.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1426131
SQL> SELECT XID,COMMIT_SCN,TABLE_NAME,START_TIMESTAMP,TABLE_OWNER,OPERATION,UNDO_SQL
from FLASHBACK_TRANSACTION_QUERY Q WHERE Q.XID in
(SELECT VERSIONS_XID FROM test VERSIONS BETWEEN SCN 1426102 and 1426131);
XID COMMIT_SCN TABLE_NAME START_TIM TABLE_OWNER OPERATIO NUNDO_SQL
-----------------------------------------------------------------------------------
08000C0094030000 1426127 TEST 03-FEB-14 SYS DELETE
insert into "SYS"."TEST"("A") values ('12');
08000C0094030000 1426127 TEST 03-FEB-14 SYS INSERT
delete from "SYS"."TEST" where ROWID = 'AAASi1AABAAAWSRAAD';
08000C0094030000 1426127 TEST 03-FEB-14 SYS INSERT
delete from "SYS"."TEST" where ROWID = 'AAASi1AABAAAWSRAAC';
08000C0094030000 1426127 TEST 03-FEB-14 SYS INSERT
delete from "SYS"."TEST" where ROWID = 'AAASi1AABAAAWSRAAB';
08000C0094030000 1426127 TEST 03-FEB-14 SYS INSERT
delete from "SYS"."TEST" where ROWID = 'AAASi1AABAAAWSRAAA';
08000C0094030000 1426127 03-FEB-14 BEGIN
6 rows selected.
这里用SCN 指定的时间点,同样可以用TIMESTAMP指定时间点就行查询。
【小结】
FLASHBACK_TRANSACTION_QUERY可以查到闪回事务的所有信息,可以用SCN和TIMESTAMP指定时间点,就没有DBA_TRANSACTION_QUERY 因此选择BC
【答案】 BC
相关参考
http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_5125.htm#REFRN29086
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29435844/viewspace-1078503/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29435844/viewspace-1078503/