Recover a Dropped, Truncated or Deleted Oracle Table Without Backup

A wrong DROP TABLE, a TRUNCATE run against production instead of test, or a DELETE without a WHERE clause: in Oracle each of these takes a second, and the data seems gone. It usually is not. Oracle does not overwrite the rows when a table is dropped, truncated or emptied; it only marks the space as free. Until new data is written there, the rows are still in the datafile blocks. This guide explains what to do first, which Oracle features can undo each mistake, and how the table is recovered from the blocks when there is no backup and Flashback cannot help.

Every insert into the same tablespace can reuse the blocks that still hold the lost rows. Stop the application or the batch jobs that write to that tablespace. If the table had a tablespace of its own, taking that tablespace offline stops all writes to it at once; if it shares a tablespace with busy tables, that is a decision for the business, because those tables go offline too. Do not run a shrink, MOVE, rebuild or reorganisation, and do not create a new table under the same name and load it with data.

Then copy the datafiles of that tablespace, together with the SYSTEM datafile, which holds the data dictionary, to another disk. A copy taken with the database shut down or the tablespace offline is consistent; a copy of open, busy files can still be read block by block, but it is better than no copy. All further work happens on the copies.

Since Oracle 10g, DROP TABLE normally moves the table into the recycle bin instead of removing it. Query USER_RECYCLEBIN, or DBA_RECYCLEBIN as a DBA: if the table is listed, FLASHBACK TABLE name TO BEFORE DROP brings it back with its rows, and RENAME TO gives it a different name if a new table already uses the old one. Its indexes and triggers come back with system-generated names and need renaming.

The recycle bin does not help when the table was dropped with PURGE, when someone ran PURGE RECYCLEBIN, when Oracle purged it automatically because the tablespace ran short of space, when the recycle bin parameter is off, or when the table lived in the SYSTEM tablespace. In all these cases the table's entries are removed from the data dictionary, but its blocks are still in the datafile until they are reused.

After a committed DELETE, Oracle can still show the table as it was a little earlier: SELECT * FROM table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE) returns the old rows, and an INSERT ... SELECT with the same clause puts them back. FLASHBACK TABLE name TO TIMESTAMP does the same for the whole table, if row movement is enabled on it. Both read the old row versions from the undo tablespace, and undo is kept only for a limited time: UNDO_RETENTION is 900 seconds by default, and busy systems overwrite undo quickly. If the query fails with ORA-01555, snapshot too old, the undo for that time is gone.

When undo is gone, the deleted rows are often still there. A DELETE does not wipe a row; it sets a flag in the row header saying the row is deleted. The row stays in the block until Oracle needs the space for new rows, so on a table that receives few inserts, most of the deleted rows can be read back weeks later.

TRUNCATE is DDL. It writes no undo for the rows, it cannot be rolled back, and Flashback Query across it fails with ORA-01466 because the table definition has changed. What TRUNCATE actually does is reset the table's high-water mark, release its extents and give the segment a new data object number. The rows themselves are not touched: they stay in the old blocks, labelled with the old data object number, until those blocks are reused by this or another table.

If the database runs in ARCHIVELOG mode with backups, there are Oracle-side options. Flashback Database rewinds the whole database to a time before the mistake, if flashback logging was switched on beforehand. RMAN can restore an auxiliary copy to a point in time, and since Oracle 12c RMAN RECOVER TABLE does this for a single table and imports it back. Both need a backup taken before the truncate and every archived log since; and Flashback Database also rewinds every other change made since that time.

When none of the above applies, recovery works directly on the copied datafiles, without opening the database. Every Oracle data block carries a header with the data object number of the table it belongs to, followed by its rows. The recovery reads the data dictionary from the SYSTEM datafile to learn the table's columns and old object number, collects every block that still carries that number, and decodes the rows, including the rows flagged as deleted. For a dropped and purged table, the dictionary rows that described it are themselves deleted rows in the dictionary tables, and they are recovered the same way; if they have already been overwritten, the columns are worked out from the data, or taken from an old export or the application's DDL.

Before we trust this on a customer's database, we test it on an Oracle database whose contents we know exactly. On our Oracle 21c test database, the deleted rows, a truncated table, a truncated partition, a dropped-and-purged table and a table in the recycle bin were each read back completely and matched the original data row for row. On a real database the result depends on one thing above all: how much has been written to the tablespace since the mistake. That is why the copy in the first step matters more than anything that follows.

Recovered rows can be exported to CSV, a SQL script or SQL*Loader files, or loaded straight into a running Oracle database under a new table name, so they can be compared with what is in production before anything is merged back.

AS Oracle Recovery Studio reads Oracle datafiles directly, with no Oracle installation needed, and recovers deleted rows, truncated tables and partitions, and dropped tables from blocks Oracle has not reused. The free edition scans the files and shows every table and row it finds, so you see exactly what can be recovered before you buy a licence to export it.

For production databases, ERP systems and cases where the dictionary is damaged too, our Oracle database recovery service does the work remotely or in our laboratory. We have recovered many Oracle and other database cases; contact us for a free assessment of your files, and if no data can be recovered, there is no charge. Afterwards, the lasting fix is simple: keep the recycle bin on, size UNDO_RETENTION for the mistakes you want to undo, run in ARCHIVELOG mode with RMAN backups, and test a restore on a schedule.