Right a Wrong in 10g – Undo transactions using FLASHBACK_TRANSACTION_QUERY

Among the topics we discuss in the Oracle 7Up Workshop at AMIS is the Flashback functionality in 9i and 10g databases. We look in depth at Flashback Query and Flashback Session (dbms_flashback.enable_at_timestamp or .enable_at_scn), at Flashback Table and Flashback Table to before undrop. And this week we also took a brief look at how you can undo the results of a certain transaction, not just by using something like update table set col = (select col from table as of timestampe sysdate - 10/60/24), which works but can be somewhat elaorate to write in full. In this case, we want to take another step and make use of the view FLASHBACK_TRANSACTION_QUERY that has an UNDO_SQL column! Note that erroneously in some publications based on the 10g Beta release this view is called DBA_TRANSACTION_QUERY (I spent quite some time looking for that darned thing!).

I will show in this article how to undo the following changes:

The example

I make some ludicrous changes in the EMP table of the SCOTT schema:

insert into emp (empno, ename, deptno, job, sal) values (1010,'TOBIAS',10,'KLEUTER', 10)
/
update emp set sal = sal * 1.3
/
delete from emp where job='MANAGER'
/

and then I commit:

commit
/

This commits the changes made in this transaction. The data in my table is changed. But not for good as we will see.

In 10g I can use the following query – with the pseudocolumns versions_startscn, versions_endscn, versions_operation and versions_xid – to show the history of the data in my table, in so far still available from the UNDO data. Note that if I wait too long, I may not find the previous state of my records as it may have been moved out of the UNDO area.

SELECT versions_xid
,      versions_startscn
,      versions_endscn
,      DECODE( versions_operation
         , 'I', 'Insert'
         , 'U', 'Update'
         , 'D', 'Delete'
              , 'Original'
             ) "Operation"
,      ename
,      sal
FROM   emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
/

I have used the VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE clause to specify that I want to look at the history of EMP. With MINVALUE and MAXVALUE you can indicate that you want the maximum history still available from the UNDO_DATA. Instead of these keywords, you can also specify explicit values for SCN, such as 1937296253. Alternatively, you can use TIMESTAMP instead of SCN.

The result of this query is shown below:

VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN Operatio ENAME             SAL
---------------- ----------------- --------------- -------- ---------- ----------
040024006B4B0000        1937296253                 Insert   TOBIAS             13
040024006B4B0000        1937296253                 Delete   CLARK            9800
040024006B4B0000        1937296253                 Delete   BLAKE           11400
040024006B4B0000        1937296253                 Delete   JONES           12495
040024006B4B0000        1937296253                 Update   MILLER           1690
040024006B4B0000        1937296253                 Update   FORD            16380
040024006B4B0000        1937296253                 Update   JAMES            4940
040024006B4B0000        1937296253                 Update   TURNER           7800
040024006B4B0000        1937296253                 Update   ADAMS            6006
040024006B4B0000        1937296253                 Update   KING             4160
040024006B4B0000        1937296253                 Update   SCOTT           16380
040024006B4B0000        1937296253                 Update   MARTIN           6500
040024006B4B0000        1937296253                 Update   WARD             6500
040024006B4B0000        1937296253                 Update   ALLEN            8320
040024006B4B0000        1937296253                 Update   SMITH            4368
                                        1937296253 Original SMITH            3360
                                        1937296253 Original ALLEN            6400
                                        1937296253 Original WARD             5000
                                        1937296253 Original JONES           12495
                                        1937296253 Original MARTIN           5000
                                        1937296253 Original BLAKE           11400
                                        1937296253 Original CLARK            9800
                                        1937296253 Original SCOTT           12600
                                        1937296253 Original KING             3200
                                        1937296253 Original TURNER           6000
                                        1937296253 Original ADAMS            4620
                                        1937296253 Original JAMES            3800
                                        1937296253 Original FORD            12600
                                        1937296253 Original MILLER           1300

29 rows selected.

Note that the record for the new Employee, Tobias, shows up only once, with a salary of 13. His initial salary of 10 was never committed, so it does not exist in the transaction history.

We can now find the UNDO_SQL for transaction 040024006B4B0000 , the value found in the VERSIONS_XID column, from the FLASHBACK_TRANSACTION_QUERY view:

SET PAGESIZE 120
SET LINESIZE 100
TTITLE 'Current FLASHBACK_TRANSACTION_QUERY Contents For Selected Employees'
COL xid                 FORMAT A16      HEADING 'XID#'
COL commit_scn          FORMAT 99999999 HEADING 'Commit|SCN'
COL operation           FORMAT A10      HEADING 'Operation'
COL logon_user          FORMAT A06      HEADING 'User|Logon'
COL table_owner         FORMAT A06      HEADING 'Table|Owner'
COL table_name          FORMAT A12      HEADING 'Table Name'
COL undo_sql            FORMAT A80     HEADING 'UNDO SQL'

SELECT operation
,      logon_user
,      commit_scn
,      undo_sql
FROM   flashback_transaction_query
WHERE  xid = '040024006B4B0000'

The results look like this:

Fri Apr 22
                Current FLASHBACK_TRANSACTION_QUERY Contents For Selected Employee

                            User   Table                  Commit
XID#             Operation  Logon  Owner  Table Name         SCN
---------------- ---------- ------ ------ ------------ ---------
UNDO SQL
--------------------------------------------------------------------------------
040024006B4B0000 DELETE     SCOTT  SCOTT  EMP          #########
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
EPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'
),'12740',NULL,'10');
040024006B4B0000 UPDATE     SCOTT  SCOTT  EMP          #########
update "SCOTT"."EMP" set "SAL" = '1300' where ROWID = 'AAAUmPAAEAAABVXAAN';
040024006B4B0000 INSERT     SCOTT  SCOTT  EMP          #########
delete from "SCOTT"."EMP" where ROWID = 'AAAUmPAAEAAABVWAAA';

Several things are clear: the original transaction contained only three statements that acted on multiple records. The UNDO_SQL is created per record. The reason is simple: it is derived from comparing values before and after the transaction, not by interpreting the statement history. Since a record can be changed many times by different statements in a single transaction, this is only logical. We see that an INSERT operation is undone by a delete – no surprise – as well as a DELETE by an INSERT. Here we see illustrated the fact that Bulk Deletes generate so much UNDO log data – and a truncate table is much faster!

We can apply individual UNDO_SQL statements to return to the situation before our somewhat frivolous transaction. We could also write a small piece of PL/SQL that will do that for us.

begin
  for r_undo in (SELECT operation
                 ,      logon_user
                 ,      commit_scn
                 ,      substr(undo_sql, 1, length(undo_sql) -1) undo_sql -- made this change inspired by Wilfred's comment to strip off the semi-colon
                 FROM   flashback_transaction_query
                 WHERE  xid = '040024006B4B0000'
                 ) loop
   execute immediate r_undo.undo_sql;
  end loop;
end;

Note: although this code seems correct to me, when I run it, I get ORA-00911: invalid character errors. So I had to resort to application of indivual SQL statements. I am not sure where these errors come from.

Resources

An excellent article that I found when I had already completed most of this post: Oracle 10g Availability Enhancements, Part 3: FLASHBACK Enhancements by Jim Czuprynski