Restore deleted data on the current day

 

In December 2, I deleted the database by mistake. The boss was so upset that I would recover the database within 30 minutes. In a hurry, I wrote a stored procedure, this method is applicable when the table is deleted on the day of restoration without remembering the table name.

 

You only need to input the time of the current day, and then call it.

 

SQL code

The stored procedure is as follows:

Create or replace procedure proc_databack (deletetime in varchar2)

As

-- Query information deleted on the current day and put it into the cursor

Cursor mycursor is (select object_name from recyclebin where droptime like deletetime );

Temp_emp varchar2 (2000 );

Vflash_back varchar2 (2000 );

Begin

Open mycursor;

Loop

Fetch mycursor into temp_emp;

Exit when mycursor % notfound;

-- Build recovery statement

Vflash_back: = 'flashback table "'| temp_emp |'" to before drop ';

-- Restores the deleted table cyclically until all the tables are restored.

Execute immediate vflash_back;

End loop;

Close mycursor;

End;

 

-- Call a stored procedure

-- For example, if today is 2011-12-02, the statement is as follows:

/*

Declare

Time varchar2 (20 );

Begin

Time: = '2014-12-2011 ';

Proc_databack (time );

End;

*/

 

From FEP baby