| Another new flashback query feature introduced with Oracle 10g is flashback transaction query. The new flashback transaction query feature allows users to review and/or recover modifications made to the database at the transaction levelmeaning that users can use this new feature to diagnose and resolve logical problems, perform historical analysis, or even audit transactions from a single transaction or by all the transactions during a period of time. Prior to 10g, DBAs had to mine redo logs with LogMiner to extract specific transactional information to possibly correct or audit SQL statements. Now, with flashback transaction query, you can undo the effects of a bad transaction much more quickly than before by querying FLASHBACK_TRANSACTION_QUERY. The undo SQL generated by this new feature can be used to roll back changes made by a transaction. Flashback transaction query can be used to retrieve historical transaction data over a certain time frame via SCN or a time value. Like other flashback features, flashback transaction query utilizes undo data to retrieve the modification history for quick retrieval performance. Flashback transaction query is also limited by the setting of UNDO_RETENTION, so you should set this parameter based on your needed timeline. Flashback transaction query also requires the database to have supplemental logging enabled. When you enable supplemental logging, additional information is logged in the undo segments and redo log files. To enable supplemental logging, issue the following SQL command: SQL> alter database add supplemental log data;
You can also query V$DATABASE to ensure that supplemental logging is enabled: SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES
Flashback Transaction Query in Action Before you can start using the flashback transaction query feature, you must grant the SELECT ANY TRANSACTION and FLASHBACK ANY TABLE system privileges in order to be able to issue queries against the FLASHBACK_TRANSACTION_QUERY table. You can use one of the following tools to retrieve transaction history using flashback transaction query: Following is a brief example of using FLASHBACK_TRANSACTION_QUERY via SQL: 1. | Create your sample data and grant necessary privileges:
SQL> create table flashtest as select * from hr.employees; Table created. SQL> grant flashback any table, select any transaction to stroupe;
| 2. | Initiate modifications to rows in the table:
SQL> select first_name, email from flashtest where department_id = 20; FIRST_NAME EMAIL -------------------- ------------------------- Michael MHARTSTE Pat PFAY SQL> delete from flashtest where department_id = 20; 2 rows deleted. SQL> commit;
| 3. | Review the transaction history from FLASHBACK_TRANSACTION_QUERY:
[View full width] SQL> select max(start_scn), max(commit_scn) from flashback_transaction_query where  logon_user = 'STROUPE'; MAX(START_SCN) MAX(COMMIT_SCN) -------------- --------------- 955272 955280 SQL> select operation, table_name, undo_sql from flashback_transaction_query where start_scn = 955272 and commit_scn = 955280; OPERATION TABLE_NAME UNDO_SQL --------------- -------------- --------------------------------------------------- DELETE FLASHTEST insert into "STROUPE"."FLASHTEST" ("EMPLOYEE_ID","FIRST_NAME", "LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID", "SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID") values ('202','Pat','Fay' ,'PFAY@FINDEPT','603.123.6666',TO_DATE('17-AUG-97', 'DD-MON-RR'),'MK_REP','6000',NULL,'201' ,'20'); DELETE FLASHTEST insert into "STROUPE"."FLASHTEST" ("EMPLOYEE_ID","FIRST_NAME", "LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID", "SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID") values ('201','Michael' ,'Hartstein','MHARTSTE@FINDEPT','515.123.5555',TO_DATE('17-FEB-96', 'DD-MON-RR'),'MK_MAN' ,'13000',NULL,'100','20');
|
Flashback transaction query can also be used in conjunction with flashback versions query to provide a higher level of detail, as detailed in the following example. The VERSIONS_XID value from a flashback versions query can be used to retrieve additional information about a transaction with flashback transaction query. [View full width] SQL> select versions_startscn, versions_endscn, versions_xid, versions_operation, 2 first_name, email from flashtest VERSIONS BETWEEN SCN MINVALUE and MAXVALUE 3 where department_id = 30; VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V FIRST_NAME EMAIL ----------------- --------------- ---------------- - -------------------- ------------------------- 982338 07002C00A1000000 D Karen KCOLMENA 982338 07002C00A1000000 D Guy GHIMURO 982338 07002C00A1000000 D Sigal STOBIAS 982338 07002C00A1000000 D Shelli SBAIDA 982338 07002C00A1000000 D Alexander AKHOO 982338 07002C00A1000000 D Den DRAPHEAL 982338 Den DRAPHEAL 982338 Alexander AKHOO 982338 Shelli SBAIDA 982338 Sigal STOBIAS 982338 Guy GHIMURO 982338 Karen KCOLMENA 12 rows selected. SQL> select operation, table_name, undo_sql from flashback_transaction_query where xid = '07002C00A1000000'; OPERATION TABLE_NAME UNDO_SQL --------------- -------------- -------------------------------------------------- DELETE FLASHTEST insert into "STROUPE"."FLASHTEST"("EMPLOYEE_ID","F IRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIR E_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGE R_ID","DEPARTMENT_ID") values ('119','Karen','Colm enares','KCOLMENA','515.127.4566',TO_DATE('10-AUG- 99', 'DD-MON-RR'),'PU_CLERK','2500',NULL,'114','30 '); DELETE FLASHTEST insert into "STROUPE"."FLASHTEST"("EMPLOYEE_ID","F IRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIR E_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGE R_ID","DEPARTMENT_ID") values ('118','Guy','Himuro ','GHIMURO','515.127.4565',TO_DATE('15-NOV-98', 'D D-MON-RR'),'PU_CLERK','2600',NULL,'114','30'); ...
Enterprise Manager also provides the capability to easily find detailed information using flashback transaction query, as detailed the following steps: 1. | Using the table from the previous example, introduce a bad transaction:
SQL> delete from flashtest where department_id = 30;
| 2. | Use OEM to review the changes by clicking on the Administration tab and the table's link under the Schema subheading.
| 3. | When you select your target table, choose the Flashback Table action from the Actions drop-down list, then click Go.
| 4. | Choose any columns you wish, add the bind row value to set the query limit, and click Next.
| 5. | On the Choose SCN page, shown in Figure 17.23, you can see the entire row history (the same information you reviewed for flashback versions query). To review the transaction information, click on a specific transaction ID.
Figure 17.23. Reviewing the Flashback Versions Query Result Set from 10g OEM Recovery wizard. 
| 6. | After you click on the transaction ID, you can review the operation, table owner, table name, and undo SQL (the same information you can select manually from FLASHBACK_TRANSACTION_QUERY), as shown in Figure 17.24.
Figure 17.24. Transaction ID Details from 10g OEM Recovery wizard. 
|
|
