How to Recover From an Accidental Full‑Table Update in Oracle

Background

During routine system operations a business change required updating only a few rows in tab_order, but a malformed UPDATE statement caused the status column of the whole table (≈100 million rows, 30 GB) to be overwritten, consuming the undo tablespace and running for 40 minutes.

UPDATE tab_order a set a.status = '01'
WHERE EXISTS (
  SELECT order_id
  FROM tab_tmp b,
       tab_order c
  WHERE b.cust_no = c.cust_no
    AND b.state = 3
);

Recovery Options

1. Rollback the transaction (if not yet committed)

Oracle uses manual commit, so if the session is still open you can issue a rollback; other databases may require explicitly starting a transaction.

SQL> begin;  /* MySQL etc. can use BEGIN to start a transaction; Oracle does not need it */
SQL> UPDATE tab_order a set a.status = '01' ... ;
SQL> rollback;

2. Use Flashback Query

If the transaction has been committed and the undo tablespace still retains the before‑image, a flashback query can retrieve the original rows.

SQL> SELECT * FROM tab_order a
     AS OF TIMESTAMP TO_TIMESTAMP('2023-03-27 21:10:00','YYYY-MM-DD HH24:MI:SS');
/* The timestamp must be earlier than the UPDATE; if undo is overwritten ORA‑01555 is raised. */
SQL> CREATE TABLE tab_old AS SELECT * FROM tab_order a
     AS OF TIMESTAMP TO_TIMESTAMP('2023-03-27 21:10:00','YYYY-MM-DD HH24:MI:SS');

3. Use LogMiner to extract undo SQL

When flashback is unavailable but the redo logs contain the original values, LogMiner can mine the logs and generate undo statements.

execute dbms_logmnr.add_logfile('/opt/oracle/archive/1_85_782895629.dbf', dbms_logmnr.new);
select LOW_TIME, HIGH_TIME, LOW_SCN, NEXT_SCN from v$logmnr_logs;
execute dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
select username, sql_redo, sql_undo
  from v$logmnr_contents
 where operation = 'UPDATE'
   and sql_redo like '%tab_order a%';

4. Restore from backup (RMAN partial tablespace recovery)

If none of the above are feasible, a point‑in‑time restore using RMAN backups can be performed, focusing on the tablespaces that contain the affected table to speed up the process.

# Example RMAN script (partial tablespace restore)
run {
  allocate channel ch1 type 'SBT_TAPE';
  allocate channel ch2 type 'SBT_TAPE';
  send 'nb_ora_serv=nbu01pri01bak01';
  send 'nb_ora_client=racdb01';
  set newname for database to '/u01/app/oracle/oradata/crm/%b';
  set until time "to_date('2023-03-27 21:10:00','yyyy-mm-dd hh24:mi:ss')";
  restore tablespace system, sysaux, UNDOTBS1, UNDOTBS2, TBS_ORCL;
  switch datafile all;
  recover database skip tablespace TBS_HB, TBS_AUDIT, TBS_OGG, USERS;
  release channel ch1;
  release channel ch2;
}
# Export the restored table and re‑apply the correct status values
exp "'/ as sysdba'" table=userA.tab_order file=tab_order.dmp
imp "'/ as sysdba'" file=tab_order.dmp fromuser=userA touser=userB;
UPDATE userA.tab_order a, userB.tab_order b
   SET a.status = b.status
 WHERE a.id = b.id;

Conclusion

The four methods are listed in order of increasing recovery cost; practitioners can choose the most appropriate one based on transaction state, undo retention, log availability, and backup strategy.