How to Recover Deleted Rows in Oracle — Simplified

Here is a comprehensive guide on using Oracle flashback query to recover deleted data, along with practical steps and SQL queries

Recently in our workshop, our team member deleted all the rows from a table. Though we issued the Rollback command, we couldn’t restore the data. However, using the Oracle flashback query concept, we did recover the data. Here are the steps helpful for your reference.

Table of contents

Recover Deleted Rows in Oracle — Photo by Daniel Reche on Pexels.com

Create a Table

create table emp(    
  empno    number(4,0),    
  ename    varchar2(10),    
  job      varchar2(9),    
  mgr      number(4,0),    
  hiredate date,    
  sal      number(7,2),    
  comm     number(7,2),    
  deptno   number(2,0),    
  constraint pk_emp primary key (empno),    

)

Insert some rows into the created Table

insert into emp   
values(   
 7698, 'BLAKE', 'MANAGER', 7839,   
 to_date('1-5-1981','dd-mm-yyyy'),   
 2850, null, 30   
)

--
insert into emp   
values(   
 7566, 'JONES', 'MANAGER', 7839,   
 to_date('2-4-1981','dd-mm-yyyy'),   
 2975, null, 20   
)

Run Delete SQL query

DELETE FROM emp a
WHERE ROWID = (
 SELECT MAX(ROWID)
 FROM emp b
 WHERE a.empno = b.empno
);

Use flashback query

SELECT * FROM emp AS OF TIMESTAMP (SYSDATE-1/24)

Create new table & write data from flashback area to new Table

CREATE TABLE emp1 AS 
SELECT * FROM emp AS OF TIMESTAMP (SYSDATE-1/24) 
WHERE 1=1

Copy data from the new table to the old Table

insert into emp 
select * from emp1 
where 1=1

Conclusion

The Oracle flashback query is a powerful way to recover deleted data. By following the steps mentioned, you can successfully recover deleted rows from a table in Oracle. It’s important to use the flashback query and table operations to restore data without using traditional rollback commands. This approach helps maintain data integrity and recovery, making it a valuable resource for Oracle database administrators and developers.

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.