Flashback Version Query and Transaction Query for Change Investigation

Learning outcomes

ServiceHub discovers that case 701 changed from OPEN to HOLD, then to CLOSED, and was later deleted. A single AS OF query answers only one point in time. Flashback Version Query returns the committed row versions across an interval and exposes the transaction identifier that produced each version. Flashback Transaction Query can add user/transaction/undo-SQL metadata—but current 26ai licensing does not permit that feature in Oracle AI Database Free.

1. Version Query returns committed lifespans

VERSIONS BETWEEN returns row versions that existed during an SCN/time interval. A new committed version is created by a transaction that changes the row. The pseudocolumns describe when a version began/ended, whether the change was an insert/update/delete, and which transaction identifier (VERSIONS_XID) created it.

2. Reproducible Free timeline lab

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_version_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_version_case (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);-- Oracle documentation recommends allowing time after CREATE TABLE-- before version-query test transactions.BEGIN DBMS_SESSION.SLEEP(15); END;/VARIABLE start_scn NUMBERBEGIN :start_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; END;/INSERT INTO servicehub_version_case VALUES (701,'OPEN',100);COMMIT;UPDATE servicehub_version_case SET status_code='HOLD',amount=150 WHERE case_id=701;COMMIT;UPDATE servicehub_version_case SET status_code='CLOSED',amount=175 WHERE case_id=701;COMMIT;DELETE FROM servicehub_version_case WHERE case_id=701;COMMIT;VARIABLE end_scn NUMBERBEGIN :end_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; END;/

3. Build the version timeline

SELECT  versions_startscn AS start_scn,  versions_endscn   AS end_scn,  versions_operation AS op,  RAWTOHEX(versions_xid) AS xid_hex,  case_id,  status_code,  amountFROM servicehub_version_caseVERSIONS BETWEEN SCN :start_scn AND :end_scnWHERE case_id=701ORDER BY versions_startscn NULLS FIRST;

Expected history contains the inserted/updated/deleted lifespans while the necessary history remains available. A version row with delete operation represents the row version involved in deletion; the current query after the final commit returns no case 701.

4. The XID is the bridge to transaction metadata

On an offering entitled to Flashback Transaction Query, FLASHBACK_TRANSACTION_QUERY can show transaction start/commit SCNs and times, logon user, operation, table/row identifiers, and generated UNDO_SQL. Oracle requires the powerful SELECT ANY TRANSACTION privilege to query this view.

SELECT  xid,  start_scn,  commit_scn,  start_timestamp,  commit_timestamp,  logon_user,  operation,  table_owner,  table_name,  row_id,  undo_sqlFROM flashback_transaction_queryWHERE xid = HEXTORAW(:xid_hex);

Oracle explicitly warns that scanning FLASHBACK_TRANSACTION_QUERY without an XID predicate can scan many unrelated rows. Narrow the investigation to transaction identifiers discovered from Version Query or other evidence.

5. Supplemental logging affects transaction-query fidelity

Current Oracle documentation says the database should have at least minimal supplemental logging enabled to avoid unpredictable Flashback Transaction Query behavior. Additional key/column logging can be required for reliable dependency/undo SQL reconstruction.

SELECT supplemental_log_data_min,       supplemental_log_data_pk,       supplemental_log_data_ui,       supplemental_log_data_fk,       supplemental_log_data_allFROM v$database;

Enabling supplemental logging is a CDB/database logging-policy change that increases redo. Do not enable it only to satisfy a lab; coordinate it with GoldenGate/Data Guard/audit/change-data-capture needs and redo capacity.

6. UNDO_SQL is evidence, not an automatic repair script

The generated UNDO_SQL represents the logical inverse of a DML operation when enough information exists. Executing it blindly can violate current constraints or overwrite later legitimate transactions. Flashback Transaction Backout is a separate feature that evaluates dependencies and applies compensating transactions; it too is not licensed in Free.

7. Deliberately wrong: treat Version Query as a permanent audit log

Undo-backed row versions can expire. Version Query may show less history tomorrow than today. Even long-term Flashback Time Travel has a configured retention/purge lifecycle and is not the same as a security audit trail. Regulatory attribution usually needs Unified Auditing/application identity/context plus controlled retention.

SELECT *FROM (  SELECT    begin_time,end_time,tuned_undoretention,    maxquerylen,ssolderrcnt,undoblks  FROM v$undostat  ORDER BY end_time DESC)FETCH FIRST 12 ROWS ONLY;

8. Cleanup

DROP TABLE servicehub_version_case PURGE;

9. Production judgment

Use Version Query quickly after an incident to reconstruct the committed row timeline and extract XIDs. Preserve findings outside volatile undo before evidence expires. Use Transaction Query only on an entitled offering, grant SELECT ANY TRANSACTION to a tightly controlled forensic role, and enable supplemental logging only as part of an intentional logging architecture.

No pack or restart is required for Version Query. Lesson 3 turns investigation into recovery: Flashback Table rewinds a table's current data using undo, while Flashback Drop retrieves a dropped object from the recycle bin without using the same undo mechanism.

Authoritative references

• Using Oracle Flashback Technology — Version Query and Transaction Query workflow/privileges
• SELECT — VERSIONS clause and pseudocolumn behavior
• FLASHBACK_TRANSACTION_QUERY — transaction metadata and supplemental logging note
• GRANT — SELECT ANY TRANSACTION privilege
• Licensing Information — Free/EE flashback feature matrix