Oracle Flashback Query (Specifying SCN vs. Timestamp)

When operating an Oracle Database, you often encounter situations where you need to “immediately check data before an update” or “investigate the contents of a record that disappeared unnoticed.” In such cases, Flashback Query is an invaluable tool that allows you to query data as it existed at a specific point in the past.

In this article, we will explain both the SCN (System Change Number) method, which allows for precise control, and the TIMESTAMP method, which is intuitive and easy to understand.

◆ What is Flashback Query?
◆ Basic Syntax
1. Specifying by SCN
2. Specifying by TIMESTAMP
◆ Practical Exercise: Trying Out Flashback Query
◆ Chronological Image
◆ FAQ
◆ Operational Precautions
◆ Summary

◆ What is Flashback Query?

While a standard SELECT statement retrieves currently committed data, a Flashback Query looks at UNDO (pre-change) data to project the state of the database at any arbitrary point in the past.

SCN Specification: Specifies the point in time using the database’s internal sequence number. This is the most accurate method.
Timestamp Specification: Uses human-readable formats like “5 minutes ago” or a specific clock time.

◆ Basic Syntax

Specifying by SCN

SELECT ... FROM table_name AS OF SCN scn_number;

Example: Checking the contents at SCN “1234567”

SELECT * FROM emp AS OF SCN 1234567;

Specifying by TIMESTAMP

SELECT ... FROM table_name AS OF TIMESTAMP timestamp_expression;

◆ Practical Exercise: Trying Out Flashback Query

Let’s create a test table and perform an experiment to compare data before and after an update.

① Create table and insert initial data

CREATE TABLE emp_test (
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(10)
);

INSERT INTO emp_test VALUES (1001, 'TARO');
INSERT INTO emp_test VALUES (1002, 'JIRO');
COMMIT;

② Obtain a recovery point (Current SCN and Timestamp)

Before the update, we capture the information of the “point we want to return to” and make a note of it.

SELECT dbms_flashback.get_system_change_number AS current_scn,
       SYSTIMESTAMP AS current_time
  FROM dual;

Example: SCN 2283755 / Time 2025-03-22 15:01:00

③ Update data (Simulating an error)

UPDATE emp_test SET ename = 'SABURO' WHERE empno = 1002;
COMMIT;

-- The current data is now 'SABURO'
SELECT * FROM emp_test;

④ Use Flashback Query to view the “Past”

Let’s refer to the data before the update using two methods.

1. Reference by specifying SCN

SELECT * FROM emp_test AS OF SCN 2283755;

2. Reference by specifying Timestamp

SELECT * FROM emp_test AS OF TIMESTAMP TO_TIMESTAMP('2025-03-22 15:01:00', 'YYYY-MM-DD HH24:MI:SS');

Result:

In both queries, the record that is currently SABURO will be displayed as JIRO (the state before the update).

◆ Chronological Image

A reference to the past works by going back along the timeline and reading from the UNDO segments as shown below:

 Timeline ──────────────────────────→
 [SCN: 2283755]         [SCN: 2283800]
 [Time: 15:01:00]       [Time: 15:05:00]
      ↓                     ↓
 +----------+         +------------+
 | 1002:JIRO| ──Update─→ | 1002:SABURO|
 +----------+         +------------+

 Query Execution:
 SELECT * FROM emp_test AS OF SCN 2283755;
 → Result returns "JIRO"!

◆ FAQ

Q: Is there a limit to how far back I can query data?
- A: Yes. It depends on the seconds set in the initialization parameter UNDO_RETENTION. However, if the UNDO tablespace runs out of space, data may be overwritten even within that timeframe, making it unreadable (triggering ORA-01555).
Q: Which should I use: SCN or TIMESTAMP?
- A: SCN is recommended for application logic and precise troubleshooting. On the other hand, TIMESTAMP is convenient for administrators to quickly check the state of the database from “a few minutes ago.”
Q: Can I use Flashback Query on a table I have already dropped?
- A: No. If the table itself was DROPped, you cannot execute a Flashback Query against it. In that case, use the FLASHBACK TABLE ... TO BEFORE DROP feature (Flashback Drop).
Q: Does Flashback Query affect performance?
- A: Since it reads UNDO segments instead of current data blocks, the load may be higher than a normal query when referencing large amounts of past data or in environments where a high volume of UNDO segments are being generated.

◆ Operational Precautions

UNDO Retention Limits: Flashback Query relies on UNDO data. If information is overwritten after the retention period, an ORA-01555: snapshot too old error will occur.
Timestamp Precision: When specifying by time, Oracle internally maps timestamps to SCNs roughly every 3 seconds. If you need strict precision at the 1-second level, use SCN.
Time Zone Considerations: Be careful with time zone discrepancies when using AS OF TIMESTAMP. Generally, calculating based on SYSTIMESTAMP is the safest approach.

◆ Summary

Item	Description
Flashback Query	A powerful feature to easily reference past data via `SELECT`.
AS OF SCN	Allows for precise investigation by pinpointing an exact change point.
AS OF TIMESTAMP	Intuitive time-based specification, such as “X minutes ago.”
Key Prerequisite	Requires UNDO data to be retained (governed by `undo_retention`).

This article is based on Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Using Oracle Flashback Technology

How to Perform Backup and Recovery Using Oracle RMAN Commands

In Oracle Database operations, automating and streamlining backup and recovery is essential. This article explains the p…