How to Recover a Truncated Oracle Table: Tools and Step‑by‑Step Guide

1. What is TRUNCATE?

In Oracle, the TRUNCATE statement removes all rows from a table and releases the occupied space. It is faster than DELETE because it does not generate DML logs for each row, but it also cannot be flash‑backed, making recovery in production environments difficult.

2. Recovery Methods

2.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 from: http://www.hellodba.com/Download/FY_Recover_Data.zip
2. Unzip the package to obtain FY_Recover_Data.pck
3. As SYS, run the script:
   SQL> @/home/oracle/FY_Recover_Data.pck
   (creates the FY_Recover_Data package)
4. Execute the recovery procedure:
   exec fy_recover_data.recover_truncated_table('SCOTT','T');
   -- This creates tablespaces FY_REC_DATA and FY_RST_DATA
5. Insert the recovered rows back into the original table:
   SQL> insert into scott.t select * from scott.t$$;
   SQL> commit;
   -- 13 rows created, data restored

2.2 ODU Recovery

ODU (Oracle Database Unloader) is emergency‑recovery software that can rescue data when a database cannot be opened or when data has been deleted without backup. If the space of the truncated table has not been overwritten, ODU can restore the original rows.

2.3 BBED Recovery

BBED (Oracle Block Browser and Editor Tool) allows direct inspection and modification of data files. Using BBED, administrators can manually extract and restore rows from a truncated table.

2.4 GDUL Recovery

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

3. Conclusion

When a table is truncated, the key to solving the problem is to identify the root cause quickly; robust security mechanisms and well‑defined operational procedures are essential to prevent such incidents.