Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Flashback Transaction Query

Oracle Database Tips by Donald Burleson

About Oracle Flashback Transaction Query


One of the important new features of Oracle Database 10g is the Flashback Transaction Query. It is a diagnostic tool to view the changes made to the database at the transaction level. This feature will help diagnose problems, analyze and audit transactions, and recover from user or application errors. The undo SQL generated by the flashback transaction query can be used to rollback the changes made by a transaction.

Oracle10g provides the ability to easily reconstruct SQL statements that have been executed by SQL statements. Previously, you would need to use Log Miner to generate SQL redo statements that could be used to replicate SQL statements executed in the database. Now, Flashback Transaction Query can be used to reconstruct the SQL statements used to make changes in the database, and those that can be used to undo the change.

Flashback Transaction Query uses an indexed access path to get to the undo data. It is faster than the LogMiner, which mines the redo log files to obtain the undo information.

Flashback Table

The flashback table command allows you to flashback an Oracle table based on timestamp or database SCN. All flashback table operations must be at the beginning of any transaction and flashback table operations are not supported for the SYS user.

The Flashback Table allows the DBA to recover database tables to a specific point in time without restoring from a backup. With this feature, the data in the tables and all associated objects including indexes, constraints, triggers etc. are restored. This statement is executed as a single transaction. All the tables must be flashed back successfully or the entire transaction is rolled back.

The Flashback Versions Query and Flashback Transaction Query can be used in tandem to determine the appropriate flashback time. The SCN should always be noted before issuing a FLASHBACK TABLE command.

The flashback table command allows you to flashback an Oracle table based on timestamp or database SCN. All flashback table operations must be at the beginning of any transaction and flashback table operations are not supported for the SYS user.

The Flashback Transaction Query is a diagnostic tool to view the changes made to the database at the transaction level. This feature will help to diagnose problems, analyze and audit transactions, and recover from user or application errors. The undo SQL generated by the flashback transaction query can be used to rollback the changes made by a transaction. The Flashback versions Query and Flashback Transaction Query can be used in tandem to determine the appropriate flashback time.

The dba_transaction_query view stores the changes made by a transaction and the nature of changes made during a specified time. Using the transaction identifier or a time range as part of the query, this dictionary view provides information on changes made to the database by any query at any time.

When there is insufficient undo data for a transaction, the dba_transaction_query will return a row with a value of UNKNOWN in the OPERATION column.

The FLASHBACK ANY TRANSACTION system privilege is needed to issue a query against the dba_transaction_query view.

 

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.