It has happened to all of us that we have accidentally deleted a table or modified or deleted data from a table by mistake. Good news, this situation can be reversed! In this opportunity I will share first how to recover the data and then how to do the same with tables.

1.- How to recover deleted or modified data

Oracle offers us the possibility to recover data that has been accidentally deleted or modified. This depends on the DBMS_FLASHBACK defined by the database administrator. It’ is like a recycling bin, but it empties every X minutes.

Imagine that we have the following table PS_TABLA_RAFA

And at a certain moment we can think of a loan, for example the employee C000129716 but we forgot to define where and we delete all records.

We do DELETE FROM PS_TABLA_RAFA

And we give to commit, or our database has the autocommit configurated. All records have been deleted

We SELECT and we see that there is nothing.


If we execute the sentence detailed below, the lost data  will be recovered:

INSERT INTO PS_TABLA_RAFA

SELECT * FROM PS_TABLA_RAFA AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘3’ MINUTE)

The parameter INTERVAL is the time that has passed since we did the delete

This Works if we UPDATE

Check this example:

And we execute:

UPDATE  PS_TABLA_RAFA SET DESCR = ‘RAFA’ (and we don’t put where)

We execute SELECT and we see that everything has changed now.


To recover it we should do this:

UPDATE PS_TABLA_RAFA SET DESCR =

 (SELECT DESCR FROM (PS_TABLA_RAFA AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ’11’ MINUTE) ) A WHERE  A.EMPLID=PS_TABLA_RAFA.EMPLID)

 

2.- How to restore drop tables.

We create the record PS_RAFA_BORRA1 with the fields EMPLID and DESCR

And then we complete the table with data.

We delete the record with a DROP TABLE.

We create the table again with the fields EMPLID, DESCR and DESCR1.

And then we complete the table with data.

If we realize that we have crushed the previous table, to revert it, we should choose a view called Recycle Bin, which contains all the deleted objects.

If we execute the next sentence we can see it clearly:

select  * from recyclebin WHERE  original_name = ‘PS_RAFA_BORRA1’

 

If we execute the following sentence taking into account that what is in the FROM is what we have in the OBJECT_NAME of the previous sentence.

create table prueba_nueva as select * from “BIN$jTCFepMCWSLgVACQ+lXLKg==$0”;

You will have created a new table called “prueba_nueva” with all the data we had in PS_RAFA_BORRA1 the first time.

I hope you find this information useful.

 

Rafael Barcelo Algora
Latest posts by Rafael Barcelo Algora (see all)