Flashback Versions Query

Configuration:

• UNDO Tablespace

Limitations:

• Does not work if the table has undergone DDL during the specified period.

Operation:

• Row-level querying

Version:

• 10gR1 / 2003

Licensing:

• Does not require Enterprise Edition

Summary

Flashback Versions Query, introduced in Oracle 10gR1 (2003), allows auditing of changes at the row level. It is useful for identifying when a record was changed, and how. This post explains undo configuration, retention guarantee, monitoring undo usage, RAC considerations, and includes practical examples of running the Versions Query.

Flashback Versions Query

This feature allows you to query changes to records at the row level. It is extremely useful for auditing purposes when we need to know a record of a table was recently changed in the database. The query uses data from UNDO segments, so having UNDO configured correctly is the basic condition for it to work.

Note: It is possible to use Versions Query with Data Archive (Flashback Time Travel) instead of UNDO. In a future post we will talk about Flashback Time Travel.

UNDO configuration

Let's analyze four points to adapt the UNDO for the use of flashback:

1. undo_management: should be set to AUTO (we are no longer in 8i 😉).
2. undo_retention: measured in seconds. The minimum time is equal to the time of your longest transaction. If you have a job that runs for 2 hours, the undo_retention must be set to at least 2 hours, otherwise you may encounter the error ORA-01555: Snapshot Too Old. Data Pump also uses UNDO to maintain consistency (FLASHBACK_TIME, FLASHBACK_SCN, CONSISTENT=Y). This means that if the database blocks are not available in UNDO, the export will fail. Example: You have a job that takes 3 hours to run. You set undo_retention to 2 hours. If you perform an export, it will fail with ORA-01555.
3. undo_tablespace: the tablespace where undo segments for the instance reside. One undo tablespace per instance. In RAC, each instance needs its own undo tablespace.
4. Tablespace retention guarantee: Even if undo_retention is correctly set, Oracle may clean data before the retention period if there is space pressure. With the guarantee enabled, Oracle will keep data until the end of undo_retention, avoiding ORA-01555 errors. Segments are divided into ACTIVE (currently used), UNEXPIRED (finished but within retention), and EXPIRED (can be reused at any time).

UNDO Cleanup

Cleanup occurs frequently, performed by the SMON process, but it happens more often when there is space pressure. If undo_retention is smaller than the duration of operations (for example, a 3-hour export with undo_retention of 2 hours), failures will likely occur.

Undo Usage Monitoring

Scripts to monitor undo usage:

• Non-CDB: tbs_undo_usage_non-cdb.sql
• CDB: tbs_undo_usage_cdb.sql

Example of script output:

TABLESPACE_NAME               |STATUS    |MB_USAGE |PERC
------------------------------|----------|-------- |----
UNDOTBS1                      |ACTIVE    |   144.00| .00
UNDOTBS1                      |EXPIRED   | 17298.00| 9.00
UNDOTBS1                      |UNEXPIRED | 14518.00| 7.00

The ACTIVE status means that transactions are currently using the undo. UNEXPIRED means that the transaction is completed but still within undo_retention. EXPIRED means the transaction is outside of undo_retention and can be cleaned at any time.

Enabling Retention Guarantee

SQL> select tablespace_name, retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS1                       NOGUARANTEE


SQL> alter tablespace UNDOTBS1 retention guarantee;

Tablespace altered.

SQL> select tablespace_name, retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME                RETENTION
------------------------------ -----------
UNDOTBS1                       GUARANTEE

Running Flashback Versions Query

Example query:

SELECT
    hextoraw(versions_xid) xid,
    TO_CHAR(versions_starttime, 'YYYY-MM-DD HH24:MI:SS') xid_time,
    CASE
        WHEN versions_operation = 'I' THEN 'INSERT'
        WHEN versions_operation = 'U' THEN 'UPDATE'
        WHEN versions_operation = 'D' THEN 'DELETE'
    END AS operation,
    id,
    name
FROM guob2025.subscriptions
VERSIONS BETWEEN
    TIMESTAMP TO_TIMESTAMP('2025-08-20 16:30:00','YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2025-08-20 16:51:00','YYYY-MM-DD HH24:MI:SS')
WHERE id IN (2504, 2505)
ORDER BY 2;

In the example above, we are running a query on guob2025.subscriptions, and to use the FLASHBACK VERSIONS QUERY feature, we just need to use the VERSIONS BETWEEN, it will allow us to use some columns (in yellow) related to that feature, that will show us the old record details.

Example Output

XID                  XID_TIME             OPERATION   ID    NAME					
-------------------- -------------------- ---------- ------ --------------------   
0100080043030000     2025-08-20 16:34:09  INSERT      2504  Adriano Bonacin        
05001A0016030000     2025-08-20 16:35:09  INSERT      2505  Acacio Lima Rocha      
07000C0026030000     2025-08-20 16:46:45  UPDATE      2504  Bona                   
02001B0029030000     2025-08-20 16:48:01  UPDATE      2505  Lima Rocha             
020014002A030000     2025-08-20 16:50:22  DELETE      2505  Lima Rocha             
020014002A030000     2025-08-20 16:50:22  DELETE      2504  Bona                   

This way you can see the sequence of changes (INSERT, UPDATE, DELETE) over time for the selected rows.

Next Steps

In the next post, we will discuss Flashback Transaction Query to identify which database user executed the transaction and the SQL text.