 |
|
Oracle Flashback with "as of
timestamp" SQL
Oracle Tips by Burleson Consulting |
Before Flashback
Query functionality can be used by ordinary users, some actions are required
from the database administrators:
Alter system set UNDO_MANAGEMENT=AUTO;
Alter system set
UNDO_RETENTION=86400;
Grant execute on
DBMS_FLASHBACK to username;
Flashback Query is enabled and disabled using the
DBMS_FLASHBACK package. The point in time of the flashback can be specified
using the SCN or the actual time.
Using a simple example, suppose you made an inappropriate
change to the database at 11:15 AM. You could use the following command to tell
Oracle to apply all undo log images as of 11:00 AM:
EXECUTE
dbms_Flashback.Enable_At_Time('28-AUG-02 11:00:00');
In Oracle9i Release 2, the AS OF timestamp clause has been
added to the SELECT statement to enable flashback query on a specific table or
set of tables. Developers are able to specify the AS OF clause for a
single-table, multiple-tables (joins) as well as specify different times for
different tables. The AS OF timestamp clause can also be used inside INSERT or
CREATE TABLE AS SELECT statements.
Oracle flashback has an “as of timestamp” WHERE clause to
allow point-in-time SQL queries:
select *
from
(select * from emp where ept=10)
AS OF TIMESTAMP SYSDATE – 1;
