"EMPLOYEES" set "MANAGER_ID" = '123' where ROWID =
Chapter 7: Managing Transactions with Undo Tablespaces 235
'AAARAxAAFAAAAHGABO';
4011380 4011463 HR INSERT DEPARTMENTS
delete from "HR"."DEPARTMENTS" where ROWID = 'AAARAsAAFAAAAA3AAb';
4011380 4011463 HR DELETE EMPLOYEES
insert into "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME",
"LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY",
"COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID","WORK_RECORD")
values ('196','Alana','Walsh','AWALSH','650.507.9811',
TO_DATE('24-APR-98', 'DD-MON-RR'),'SH_CLERK','3100',
NULL,'124','50',NULL);
4011380 4011463 HR UPDATE EMPLOYEES
update "HR"."EMPLOYEES" set "SALARY" = '2800' where
ROWID = 'AAARAxAAFAAAAHGABO';
4011380 4011463 HR BEGIN
We confirm what we already expected??”that another user in the HR department made the
deletion and salary update (thus pointing out the usefulness of assigning separate user accounts
for each member of the HR department). The UNDO_SQL column contains the actual SQL code
that can be used to reverse the effect of the transaction. Note, however, that in this example, this
is the first transaction to occur between the SCNs of interest. If other transactions made further
updates to the same columns, we may want to review the other updates before running the SQL
code in the UNDO_SQL column.
Column Name Description
XID Transaction ID number
START_SCN SCN for the first DML in the transaction
START_TIMESTAMP Timestamp of the first DML in the transaction
COMMIT_SCN SCN when the transaction was committed
COMMIT_TIMESTAMP Timestamp when the transaction was committed
LOGON_USER User who owned the transaction
UNDO_CHANGE# Undo SCN
OPERATION DML operation performed: DELETE, INSERT, UPDATE, BEGIN, or
UNKNOWN
TABLE_NAME Table changed by DML
TABLE_OWNER Owner of the table changed by DML
ROW_ID ROWID of the row modified by DML
UNDO_SQL SQL statement to undo the DML operation
TABLE 7-4 FLASHBACK_TRANSACTION_QUERY Columns
236 Oracle Database 11g DBA Handbook
Flashback Data Archive
Recent regulations such as Sarbanes-Oxley and HIPAA require strict control and tracking
requirements for customer and patient data; keeping a historical record of all changes to rows
in critical tables is error prone and requires custom applications or database triggers to maintain
repositories for the historical changes.
Pages:
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397