When operating an Oracle Database, there are times when you want to track the history of “when, by which transaction, and how data was rewritten.” In such cases, Flashback Version Query is an extremely useful tool.
Using this feature, you can display a list of the transitions of insertions, updates, and deletions performed on specific rows over time, aligned with SCNs or timestamps.
1. What is Flashback Version Query?
Flashback Version Query is a feature that uses UNDO data to display row-level change history occurring within a specific period (SCN or Timestamp). By simply adding the VERSIONS BETWEEN clause to a standard SELECT statement, you can retrieve the “start point,” “end point,” and “operation type (I/U/D)” of changes along with metadata.
2. Preparation: Creating a Test Table and Generating History
To actually check the history, let’s create a table and perform multiple updates.
① Create Table and Initial Data Entry
-- Create test table
CREATE TABLE emp_flash (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER
);
-- Insert initial data
INSERT INTO emp_flash VALUES (1, 'TARO', 300000);
COMMIT;
② Check Recovery Point (Current SCN)
Note the SCN that will serve as the starting point before updates.
-- Check current SCN
SELECT CURRENT_SCN FROM V$DATABASE;
-- Example: 2284694
③ Data Updates and Commits
Intentionally commit in multiple steps to generate a change history.
-- 1st Update
UPDATE emp_flash SET salary = 320000 WHERE emp_id = 1;
COMMIT;
-- 2nd Update
UPDATE emp_flash SET salary = 350000 WHERE emp_id = 1;
COMMIT;
-- 3rd Update
UPDATE emp_flash SET salary = 400000 WHERE emp_id = 1;
COMMIT;
-- Check final SCN
SELECT CURRENT_SCN FROM V$DATABASE;
-- Example: 2284735
3. Verifying History (SCN Specification)
Use VERSIONS BETWEEN SCN to check how the data changed within the specified SCN range.
SELECT
emp_id,
emp_name,
salary,
VERSIONS_STARTSCN, -- Starting SCN when change became effective
VERSIONS_ENDSCN, -- Ending SCN when change was overwritten
VERSIONS_XID, -- Transaction ID
VERSIONS_OPERATION -- Operation type (I:Insert, U:Update, D:Delete)
FROM
emp_flash VERSIONS BETWEEN SCN 2284694 AND 2284735
WHERE
emp_id = 1;
SQL> CREATE TABLE emp_flash (
2 emp_id NUMBER PRIMARY KEY,
3 emp_name VARCHAR2(50),
4 salary NUMBER
5 );
Table created.
SQL> INSERT INTO emp_flash VALUES (1, 'TARO', 300000);
1 row created.
SQL> col emp_name for a20
SQL> SELECT * FROM emp_flash;
EMP_ID EMP_NAME SALARY
---------- -------------------- ----------
1 TARO 300000
SQL> commit;
Commit complete.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2284694
SQL> UPDATE emp_flash SET salary = 320000 WHERE emp_id = 1;
1 row updated.
SQL> SELECT * FROM emp_flash;
EMP_ID EMP_NAME SALARY
---------- -------------------- ----------
1 TARO 320000
SQL> commit;
Commit complete.
SQL> UPDATE emp_flash SET salary = 350000 WHERE emp_id = 1;
1 row updated.
SQL> SELECT * FROM emp_flash;
EMP_ID EMP_NAME SALARY
---------- -------------------- ----------
1 TARO 350000
SQL> commit;
Commit complete.
SQL> UPDATE emp_flash SET salary = 400000 WHERE emp_id = 1;
1 row updated.
SQL> SELECT * FROM emp_flash;
EMP_ID EMP_NAME SALARY
---------- -------------------- ----------
1 TARO 400000
SQL> commit;
Commit complete.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
2284735
SQL> SELECT
2 emp_id,
3 emp_name,
4 salary,
5 VERSIONS_STARTSCN,
6 VERSIONS_ENDSCN,
7 VERSIONS_XID,
8 VERSIONS_OPERATION
9 FROM
10 emp_flash VERSIONS BETWEEN SCN 2284694 AND 2284735
11 WHERE
12 emp_id = 1;
EMP_ID EMP_NAME SALARY VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID VER
---------- -------------------- ---------- ----------------- --------------- ---------------- ---
1 TARO 400000 2284723 09000700F1030000 U
1 TARO 350000 2284710 2284723 08000C000E040000 U
1 TARO 320000 2284702 2284710 0A001A00FE030000 U
1 TARO 300000 2284702
Execution Result Image
| EMP_ID | EMP_NAME | SALARY | VERSIONS_STARTSCN | VERSIONS_ENDSCN | VERSIONS_OPERATION |
| 1 | TARO | 400000 | 2284723 | U | |
| 1 | TARO | 350000 | 2284710 | 2284723 | U |
| 1 | TARO | 320000 | 2284702 | 2284710 | U |
| 1 | TARO | 300000 | 2284702 |
4. Specifying History by Date and Time (Supplemental)
Instead of SCN, it is also possible to specify by a human-readable Date/Time (TIMESTAMP).
SELECT
emp_id,
emp_name,
salary,
VERSIONS_STARTTIME,
VERSIONS_ENDTIME,
VERSIONS_OPERATION
FROM
emp_flash VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2025-03-22 10:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND
TO_TIMESTAMP('2025-03-22 11:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE
emp_id = 1;
5. FAQ
Q: Why is VERSIONS_STARTSCN null for some rows?
A: It becomes null if the change for that row occurred prior to the range specified in the BETWEEN clause.
Q: Please tell me the meaning of the characters retrieved in VERSIONS_OPERATION.
A: I represents INSERT, U represents UPDATE, and D represents DELETE.
Q: How far back can I trace the history?
A: This depends on the size of the UNDO tablespace and the setting of the UNDO_RETENTION parameter. If data is overwritten beyond these limits, it cannot be referenced.
Q: What happens to the history after performing DDL (Table Definition Change)?
A: If you perform a version query across a table definition change, an error may occur, or you may not be able to obtain correct results.
6. Summary
| Item | Content |
| Main Use | Investigation of incorrect data updates, auditing, troubleshooting |
| Required Data | UNDO data (Depends on UNDO tablespace) |
| Specification Method | SCN (System Change Number) or TIMESTAMP (Date/Time) |
| Information Obtained | Values before/after change, Transaction ID, Operation type (I/U/D) |
Flashback Version Query is the ideal feature for “identifying the culprit” or “confirming data transitions.” If you find suspicious data in a production environment, start by unraveling history with this query.
This article targets Oracle Database 19c (screens or default values may vary for other versions).
[reference]
Using Oracle Flashback Technology
コメント