Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle Flashback Transaction
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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';


---------------- ---------- ------------ ------------



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;


   trans_arr := xid_array('0200030052030000','0200030052030000');

   dbms_flashback.transaction_backout (

        numtxns         => 1,

        xids            => trans_arr,

        options         => dbms_flashback.cascade





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.

Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software








r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.



BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.