8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Flashback Query (AS OF) in Oracle Database 10g
Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF
clause. Essentially it is the same as the DBMS_FLASHBACK functionality or Oracle 9i, but in a more convenient form.
Related articles.
- Flashback Query
- Flashback Query (9i)
- Flashback New Features and Enhancements in Oracle Database 10g
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
- Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Flashback Query
Create a test table with no data and check the current SCN and time.
CREATE TABLE flashback_query_test ( id NUMBER(10) ); SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM ----------- ------------------- 722452 2004-03-29 13:34:12 SQL>
Add a row to the test table and commit the change.
INSERT INTO flashback_query_test (id) VALUES (1); COMMIT;
If we check the contents of the table, we can see there is a single row.
SELECT COUNT(*) FROM flashback_query_test; COUNT(*) ---------- 1 SQL>
The following two examples use the AS OF clause to query the table using the timestamp and SCN we captured before the row was inserted.
SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS'); COUNT(*) ---------- 0 SQL> SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452; COUNT(*) ---------- 0 SQL>
As we can see, the data in the table reflect the point in time specified in the AS OF
clause.
Considerations
There are a few things to consider when using flashback query.
- Flashback query is based on UNDO. As a result, the amount of time you can flashback is dependent on how long undo information is retained, as specified by the
UNDO_RETENTION
parameter. - The flashback operation is performed by winding back the data using the undo information, like a consistent get. The time it takes to wind back the data depends on the number of changes that have happened, so flashback queries of volatile data that go back a long time can be slow.
- Flashback Data Archive (FDA) was introduced in Oracle 11g to allow you to protect UNDO information, allowing guaranteed flashback queries.
- Each table in a query can reference a different point in time.
- The
AS OF
clause can be included in DML and DDL statements.
For more information see:
- Flashback Query
- Using Flashback Query (SELECT ... AS OF)
- Flashback New Features and Enhancements in Oracle Database 10g
- Flashback Query (9i)
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
- Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...