How to Recover Data After an Oracle TRUNCATE Accident

What is TRUNCATE?

In Oracle, the TRUNCATE statement removes all rows from a table and releases the allocated space. It is faster than DELETE because it does not generate row‑level DML logs, but it cannot be rolled back with Flashback, making recovery in production environments challenging.

Recovery Methods

1. Fy_Recover_Data

Fy_Recover_Data is a pure PL/SQL package that leverages Oracle's table‑scan and data‑grafting mechanisms to recover data from a truncated or corrupted table.

1. Download the package from: http://www.hellodba.com/Download/FY_Recover_Data.zip
2. Unzip the package to obtain <em>FY_Recover_Data.pck</em>
3. As SYS, run the script (example path <em>/home/oracle/</em>):
   @/home/oracle/FY_Recover_Data.pck
   -- This creates the package FY_Recover_Data
4. Execute the recovery procedure:
   exec fy_recover_data.recover_truncated_table('SCOTT','T');
   -- This creates two tablespaces FY_REC_DATA and FY_RST_DATA
5. Insert the recovered rows back into the original table:
   insert into scott.t select * from scott.t$$;
   commit;
   -- <em>scott.t$$</em> holds the data that existed before the truncate.

2. ODU (Oracle Database Unloader)

ODU is a utility for emergency recovery when a database cannot be opened or data has been deleted without a backup. It can rescue data from a truncated table as long as the original space has not been overwritten.

3. BBED (Oracle Block Browser and Editor Tool)

BBED allows direct inspection and modification of Oracle datafiles. Using BBED, you can view the raw blocks of a truncated table and extract the lost rows.

4. GDUL (Oracle GDUL Tool)

GDUL is a free tool contributed by the Oracle DBA community. It supports multiple export formats (including expdp and exp), works with ASM file systems, handles most column types, and runs on common hardware platforms.

Conclusion

Accidental truncation of a table can happen frequently. Recovering the data depends on the availability of the original space and the use of specialized tools such as Fy_Recover_Data, ODU, BBED, or GDUL. Robust security mechanisms and disciplined operational procedures are essential to prevent such incidents.