When you accidentally commit an incorrect update in Oracle Database, using “Flashback Table” to revert an entire table can sometimes have too broad an impact. When you want to “smartly cancel only this specific update (transaction),” Flashback Transaction is the tool for the job.
In this article, we will explain the steps to identify the Transaction ID (XID) and safely restore data, including handling dependencies.
🔁 What is Flashback Transaction?
This feature allows you to specify a particular transaction and revert data by automatically executing “compensatory SQL” that negates the changes made by that transaction.
- Pinpoint Restoration: Target only specific
UPDATEorDELETEoperations. - Dependency Resolution: You can collectively undo subsequent transactions that were dependent on the initial change.
📌 Prerequisites for Execution
To use this feature, “Supplemental Logging” must be enabled beforehand.
| Item | Content / Condition |
| Automatic UNDO Management | UNDO_MANAGEMENT must be set to AUTO. |
| Supplemental Log | Minimal supplemental logging must be enabled. |
| UNDO Retention Period | UNDO information for the transaction must remain without being overwritten. |
📋 Step 1: Environment Preparation and Supplemental Log Setup
First, verify the log settings and prepare test data.
Enabling Minimal Supplemental Logging
-- Check current setting
SELECT supplemental_log_data_min FROM v$database;
-- Enable (Execute only if result is 'NO')
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Confirm setting (It should be 'YES')
SELECT supplemental_log_data_min FROM v$database;
-- Perform log switch
ALTER SYSTEM SWITCH LOGFILE;
Creating Test Table and Inserting Data
CREATE TABLE emp_test (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(20),
sal NUMBER
);
INSERT INTO emp_test VALUES (1001, 'SCOTT', 3000);
INSERT INTO emp_test VALUES (1002, 'JONES', 2500);
COMMIT;
📋 Step 2: Executing and Identifying the Erroneous Operation
Execute Erroneous Operation
Simulate a situation where “Scott’s salary was accidentally set to 0.”
UPDATE emp_test SET sal = 0 WHERE empno = 1001;
COMMIT;
Identify the Transaction ID (XID)
Query the flashback_transaction_query view, which manages historical transaction information.
-- Display settings
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
set linesize 1000
col operation for a10
col undo_sql for a80
col table_name for a10
-- Query history
SELECT xid, operation, undo_sql, table_name, start_timestamp
FROM flashback_transaction_query
WHERE table_name = 'EMP_TEST';
Identify the XID (e.g., 0800130000040000) and the corresponding UNDO_SQL for restoration.
📋 Step 3: Executing the Transaction Backout
Use the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to roll back the specific process.
-- Execute with SYSDBA privileges
conn / as sysdba
BEGIN
DBMS_FLASHBACK.TRANSACTION_BACKOUT(
numtxns => 1,
xids => SYS.XID_ARRAY('0800130000040000'), -- Specify the identified XID
options => DBMS_FLASHBACK.CASCADE -- Process dependencies together
);
END;
/
-- Verify after execution
SELECT * FROM test1.emp_test;
-- Success if SAL is back to 3000!
Execution Log Example:
SQL> SELECT supplemental_log_data_min FROM v$database;
...
YES
SQL> UPDATE emp_test SET sal = 0 WHERE empno = 1001; ★Execute wrong operation
1 row updated.
SQL> commit;
Commit complete.
SQL> SELECT xid, operation, undo_sql, table_name, start_timestamp
2 FROM flashback_transaction_query
3 WHERE table_name = 'EMP_TEST';
XID OPERATION UNDO_SQL TABLE_NAME START_TIMESTAMP
---------------- ---------- -------------------------------------------------------------------------------- ---------- -------------------
0800130000040000 UPDATE update "TEST1"."EMP_TEST" set "SAL" = '3000' where ROWID = 'AAASHRAAHAAAAF+AAA'; EMP_TEST 2025/03/23 11:25:26 ★Restore to this point
SQL> conn / as sysdba
Connected.
SQL> BEGIN
2 DBMS_FLASHBACK.TRANSACTION_BACKOUT(
3 numtxns => 1,
4 xids => SYS.XID_ARRAY('0800130000040000'),
5 options => DBMS_FLASHBACK.CASCADE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
Options for options
| Option | Content |
| NOCASCADE | Executes only if there are no dependent transactions. Errors out if they exist. |
| CASCADE | Reverts the specified transaction and all subsequent “dependent changes.” (Recommended) |
| NOCASCADE_FORCE | Ignores dependencies and forces the execution of the specified SQL only. |
🧷 What is a Dependent Transaction?
As shown in the logic below, if Transaction 2 (T2) modifies data that was already changed by Transaction 1 (T1), T2 is “dependent” on T1. Since deleting T1 alone would break data consistency, CASCADE is typically used to restore both together.
FAQ
Q1: Can a standard user execute this?
A1: Execution requires the SELECT ANY TRANSACTION privilege and execution rights for the DBMS_FLASHBACK package. Generally, a user with the DBA role performs this task.
Q2: What is the difference between this and manually copying/executing the UNDO_SQL?
A2: When using TRANSACTION_BACKOUT, Oracle automatically checks for dependencies (Foreign Key constraints or conflicts with other updates) and reverts the data while maintaining consistency. It is safer than manual execution.
Q3: Can I revert DDL (table definition changes)?
A3: No, this is limited to undoing DML (INSERT/UPDATE/DELETE) operations.
Summary
- Flashback Transaction erases specific changes with pinpoint accuracy.
- The first step is identifying the XID (Transaction ID).
- Enabling Supplemental Logging is a mandatory prerequisite.
- Utilize the
CASCADEoption to prevent inconsistencies caused by dependencies.
Verification Environment: Oracle Database 19c. It is strongly recommended to take a full backup before executing in a production environment.
Conclusion
By shifting from “full data reversion” to “erasing only specific mistakes,” your recovery flexibility increases significantly. We recommend trying this flow in a test environment to prepare for emergencies.
For even more advanced restoration methods requiring synchronization across multiple tables, you may also want to study Flashback Database.
[reference]
Using Oracle Flashback Technology
コメント