Recover deleted Oracle rows without Flashback
Five rows gone. The coffee mug included. Here’s how I got them back from the datafile with reclaimDB.
A wrong DELETE is annoying. A wrong DELETE followed by COMMIT is where the afternoon gets interesting.
If Flashback Query can still see the old rows, I would start there. This is the other route: reading deleted row images that are still present in an Oracle datafile, using reclaimdb restore --include-deleted.
1. A small table. A committed mistake.
I used Oracle 19c and a plain heap table, RECLAIMDB.DESK_KIT: an ID, an item name and a quantity. Fifteen rows are enough to see what happens.
SQL> select * from RECLAIMDB.DESK_KIT order by ID;
ID ITEM QTY
--- ---------------- ----
1 Notebook 12
2 Pencil 30
3 Pen 20
4 Eraser 8
5 Ruler 6
6 Coffee mug 5
7 USB cable 9
8 Mouse pad 7
9 Desk lamp 3
10 Headphones 4
11 Sticky notes 16
12 Paper clips 50
13 Stapler 4
14 Folder 18
15 Water bottle 6
15 rows selected.SQL> delete from RECLAIMDB.DESK_KIT where ID between 6 and 10;
5 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) as ROWS_LEFT from RECLAIMDB.DESK_KIT;
ROWS_LEFT
----------
10
1 row selected.2. Preserve the datafile.
Stop writes to the affected table and preserve the files before blocks get reused. In this lab, I took the dedicated test tablespace OFFLINE NORMAL, then copied its datafile. Recovery ran against that copy.
SQL> alter tablespace BLOG_UNDELETE_261109 offline normal;
Tablespace altered.On the recovery machine, my copy is source/desk_kit.dbf. I also copied the database’s SYSTEM datafile to read the table definition:
$ ./reclaimdb --quiet metadata --schema RECLAIMDB \
> -o metadata.json source/system.dbfThe affected tablespace stays offline until the extraction is finished. Plan that interruption for a real system; don’t make an ordinary filesystem copy of a datafile while it is being written.
3. Include the deleted rows.
A normal export returned ten rows. Adding --include-deleted returned fifteen, with an extra _DELETED column.
$ ./reclaimdb --quiet restore --include-deleted \
> -m metadata.json --tables RECLAIMDB.DESK_KIT \
> --csv-dialect rfc4180 --csv recovered source/desk_kit.dbf
Exporting 1 tables (single-pass)...
✓ DESK_KIT 15 rows → recovered/reclaimdb.desk_kit.csv
! RESTORE COMPLETE — blocks failed validation
15 rows exported to recovered (CSV, 0.0s)
4 block(s) failed validation and were skipped; rows stored in them, if any, are not in the output.This warning was a false alarm in this lab: the RC1 build counted four empty blocks as validation failures. Oracle DBVERIFY found no corruption, and all 15 rows and all 45 original values matched. A fix for the reporting is in our internal backlog. Other skip warnings still need checking.
$ cat recovered/reclaimdb.desk_kit.csv
ID,ITEM,QTY,_DELETED
1,Notebook,12,FALSE
2,Pencil,30,FALSE
3,Pen,20,FALSE
4,Eraser,8,FALSE
5,Ruler,6,FALSE
6,Coffee mug,5,TRUE
7,USB cable,9,TRUE
8,Mouse pad,7,TRUE
9,Desk lamp,3,TRUE
10,Headphones,4,TRUE
11,Sticky notes,16,FALSE
12,Paper clips,50,FALSE
13,Stapler,4,FALSE
14,Folder,18,FALSE
15,Water bottle,6,FALSEThere’s the coffee mug. The five TRUE rows are the deleted ones. The other ten are still live, so importing the whole file back into the original table would be a bad idea.
4. Bring the tablespace back online.
The extraction is finished. Before creating the staging table or inserting any rows, bring the test tablespace online again:
SQL> alter tablespace BLOG_UNDELETE_261109 online;
Tablespace altered.5. Review, then put back the five rows.
I reviewed the five TRUE rows and put their ID, item and quantity into RECLAIMDB.DESK_KIT_RECOVERED, a small staging table. For this five-row lab, I used the SQL below. The _DELETED flag stays out of the application table.
Staging SQL for these five recovered rows
SQL> create table RECLAIMDB.DESK_KIT_RECOVERED
(ID number(3) primary key, ITEM varchar2(24), QTY number(3));
Table created.
insert all
into RECLAIMDB.DESK_KIT_RECOVERED values (6,'Coffee mug',5)
into RECLAIMDB.DESK_KIT_RECOVERED values (7,'USB cable',9)
into RECLAIMDB.DESK_KIT_RECOVERED values (8,'Mouse pad',7)
into RECLAIMDB.DESK_KIT_RECOVERED values (9,'Desk lamp',3)
into RECLAIMDB.DESK_KIT_RECOVERED values (10,'Headphones',4)
select 1 from dual;
5 rows created.For a small CSV import without typing the values, SQL Developer’s Import Data wizard is another option: import a reviewed CSV containing only the five deleted rows and the three business columns into the empty staging table. Then use the same delta insert below.
With the reviewed rows in staging, I inserted only IDs that were still missing:
SQL> insert into RECLAIMDB.DESK_KIT (ID, ITEM, QTY)
select r.ID, r.ITEM, r.QTY from RECLAIMDB.DESK_KIT_RECOVERED r
where not exists (select 1 from RECLAIMDB.DESK_KIT d where d.ID = r.ID);
5 rows created.
SQL> commit;
Commit complete.What this can—and cannot—recover
This works while the deleted row images are still in the blocks. It does not reconstruct overwritten rows, and the deletion flag is not a timestamp or proof that a row belongs to your particular incident. Review the recovered values before merging them into a real table.
I tested a committed DELETE on a small, uncompressed heap table. No Flashback query, undo file or redo log was used for the extraction. I did not force undo to expire in this run; the test proves the datafile recovery path, not a simulated Flashback failure.
For the broader workflow, see offline Oracle datafile extraction and the current recovery support and limitations.
If you want to try this with your own files, request a reclaimDB evaluation.