Oracle Flashback Query (AS OF)

Oracle Flashback Query (AS OF) is a SQL statement in the Oracle Specific category. Oracle Flashback Query retrieves data as it existed at a past point in time, using undo data. The syntax is SELECT * FROM table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE);. It returns historical data. A typical example: -- View data 30 minutes ago: SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE); -- Flashback Version Query: SELECT name, salary, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION FROM employees VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP; -- Shows all versions of rows in the last hour -- Flashback Transaction Query: SELECT * FROM flashback_transaction_query WHERE table_name = 'EMPLOYEES'; -- Requirements: -- Sufficient UNDO space -- UNDO_RETENTION… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).