Effective Tips on Oracle Flashback!!

 Author: R. Nyffenegger

Page: 1 2

In this article I will discuss some great tips about Oracle flashback. You can use the as of clause in a select statement to see the data as of a past SCN or past point in time. This makes it possible to compare the current data in a table with a previous data set.

Comparing Data from two different times:

You can compare data from two different times by using the AS OF clause in self-joins, or in set operations such as INTERSECT and MINUS . The results can be stored by preceding a Flashback Query with a CREATE TABLE AS SELECT or INSERT INTO TABLE SELECT statement.

Below query re-inserts the rows that were present an hour ago in the table.

INSERT INTO employee
(SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE))
MINUS SELECT * FROM employee);

Creating View:

A view referring to past data can be created by using the AS OF clause in the SELECT statement that defines the view. The past time is recalculated for each query if you specify a relative time by subtracting from SYSDATE . After a change in daylight savings time, SYSDATE - 1 might refer to either 23 or 25 hours ago instead of 24.

CREATE VIEW hour_ago AS
SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE );

SCN vs. TIMESTAMP:

Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. Use an SCN instead of a timestamp if a possible 3-second error (maximum) is important to a Flashback Query in your application.

Flashback for DDL and DML Operations:

The AS OF clause for each table can be specified or omitted and different times for different tables can be specified. AS OF clause can be used in a query to perform DDL operations such as creating and truncating tables or DML operations such as inserting and deleting) in the same session as the query.

AS OF clause can be used inside an INSERT or CREATE TABLE AS SELECT statement in order to use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Enhance performance of Oracle Data Load
   Releasing Oracle Unused Space: Quick & Efficient Solution!!
   The Power of Oracle Data Integration Tools!!
   Common Database design mistakes – Do you avoid them?