Flashback Transaction
The
dbms_flashback.transaction_backout
procedure rolls back a transaction and all its
dependent transactions. As with all the other
flashback technologies explained so far, the
transaction back-out operation uses UNDO to
create and execute the compensating or opposite
transactions that return the affected data to
its original state. In some respects, the
granularity of this flashback operation is
somewhere between the last two cases: table and
database. Now go back to a subset of the
database that represents some logical collection
of tables and queries. It essentially implements
the prior mentioned SAVEPOINT concept in the
database rather than the application code. In
fact, flashback transactions mostly eliminate
the need for the next section on redo log file
mining since it is now transparently and more
easily done as shown below.
First, query the
flashback_transaction_query view
to see what transactions exist for whatever
objects and/or users that may have done
something that need to be undone. This view can
return a lot of information in even a mildly
busy database, so filtering is highly
recommended. For example, see what transactions
have occurred in the past day by logon user BERT
and on tables owned by BERT. Note that this view
offers the UNDO SQL code.
SQL> select xid,
start_scn, operation, table_name, undo_sql
from flashback_transaction_query where
start_timestamp>=sysdate-1 and
username='BERT' and table_owner='BERT';
XID
START_SCN OPERATION
TABLE_NAME
----------------
---------- ------------ ------------
UNDO_SQL
--------------------------------------------------------------
0200030052030000
475697 DELETE
JUNK
insert into
"BERT"."JUNK"("C1","C2") values ('5','6');
0200030052030000
475697 DELETE
JUNK
insert into
"BERT"."JUNK"("C1","C2") values ('3','4');
0200030052030000
475697 INSERT
JUNK
delete from "BERT"."JUNK"
where ROWID = 'AAAD94AAAAAAChOAAD';
0200030052030000
475697 INSERT
JUNK
delete from "BERT"."JUNK"
where ROWID = 'AAAD94AAAAAAChOAAC';
0200030052030000
475697 INSERT
JUNK
delete from "BERT"."JUNK"
where ROWID = 'AAAD94AAAAAAChOAAB';
0200030052030000
475697 INSERT
JUNK
delete from "BERT"."JUNK"
where ROWID = 'AAAD94AAAAAAChOAAA';
If one wants to undo the two delete commands
whose undo action was to reinsert the data that
was deleted, here is the PL/SQL code for doing
that.
SQL> select * from
bert.junk;
C1
C2
---------- ----------
1
2
7
8
SQL> declare
trans_arr
XID_ARRAY;
begin
trans_arr :=
xid_array('0200030052030000','0200030052030000');
dbms_flashback.transaction_backout (
numtxns
=> 1,
xids
=> trans_arr,
options
=> dbms_flashback.cascade
);
end;
/
SQL> select * from
bert.junk;
C1
C2
---------- ----------
1
2
3
4
5
6
7
8
Once again, for those who prefer a graphical
interface, OEM has a rather easy screen for
doing object level point-in-time transaction
recoveries.
Figure 6.8:
OEM
Table Screen
Figure 6.9:
OEM
Transaction Flashback Screen
Flashback Archives
The final piece of the puzzle in the flashback
progression is the new Oracle 11g flashback
archive. Define an area that provides the
ability to automatically track and archive
transactional data changes to specified database
objects. These flashback archives become user
named and managed persistence of UNDO at the
specified object level. So when one needs to do
a SELECT with an AS OF, rely on the object being
in the chosen container for the specified
duration and competing for space only with the
objects one chooses. Thus, it is
merely a named are to support all the prior
flashback features that have just been examined.
SQL> create tablespace
flash_archive
datafile 'c:\oracle\oradata\ordb1\flash_archive.dbf'
size 50M;
Tablespace created
SQL> create flashback
archive default flash_archive tablespace
flash_archive retention 30 day;
Flashback archive
created.
SQL> create table
bert.junk (c1 int, c2 int) flashback archive
flash_archive;
Table created.
