Two methods of Oracle Delete data table restore (Oracle Restore) _oracle

First, if it is just deleted, then there are two ways:

First with show parameter undo; command to view the database parameter undo_retention settings at that time.

The display is as follows:

Copy Code code as follows:

Undo_management string AUTO

Undo_retention integer 10800

Undo_suppress_errors Boolean FALSE

Undo_tablespace string UNDOTBS1

Undo_retention (holding force), 10800 units is seconds. That's 3 hours.

To modify the default undo_retention parameter settings:

Copy Code code as follows:

ALTER SYSTEM SET undo_retention=10800 scope=both;

Method 1, Oracle-provided back-flash functionality:

Copy Code code as follows:

EXEC dbms_flashback.enable_at_time (to_date (' 2007-07-23 10:21:00 ', ' yyyy-mm-dd hh24:mi:ss '));

Set Serveroutput on

DECLARE r_temp Hr.job_history%rowtype;

CURSOR C_temp is SELECT * from Hr.job_history;

BEGIN

OPEN c_temp;

dbms_flashback.disable;

LOOP

FETCH c_temp into r_temp;

EXIT when C_temp%notfound;

Insert into Hr.job_history (employee_id,job_id,start_date,end_date) VALUES (r_temp. Employee_id,r_temp. Job_id,r_temp. Start_date,r_temp. end_date);

Commit

End LOOP;

Close c_temp;

End;

Method 2,insert into Hr.job_history

Copy Code code as follows:

SELECT * from hr.job_history as of timestamp to_timestamp (' 2007-07-23 10:20:00 ', ' yyyy-mm-dd hh24:mi:ss ');

This method is simple, easy to master, the function and the same time as above for you the time before the wrong operation, preferably from the wrong operation is relatively near, because the Oracle save in the rollback retention section of the data time has a certain time limit by undo_retention This parameter value decision.

Second, if it is deleted for some time, but you have a relatively new database backup, backup to restore. Create a new library, restore the backup, export the table data, and import it into the library you are using.

Third, if the deletion for some time, and no backup, but the data is written to the table when the other related tables, then try to write SQL statements from other tables to insert data into the deleted table.

Four, restore to the backup table

Copy Code code as follows:

CREATE TABLE Tablename_bak
As
SELECT * FROM tablename as of TIMESTAMP To_timestamp (' 20081126 103435 ', ' yyyymmdd Hh24miss ');