Two Methods for restoring oracle accidentally deleted data tables (oracle restoration)

1. If it is just deleted, there are two methods:

Run the show parameter undo command to view the current database parameter undo_retention settings.

Shown as follows:
Copy codeThe Code is as follows:
Undo_management string AUTO

Undo_retention integer 10800

Undo_suppress_errors boolean FALSE

Undo_tablespace string UNDOTBS1

Undo_retention (hold power). The Unit is 10800 seconds. That is, 3 hours.

Modify the default undo_retention parameter settings:

Copy codeThe Code is as follows:
Alter system set undo_retention = 10800 SCOPE = BOTH;

Method 1: Use the flashback function provided by oracle:
Copy codeThe Code is as follows:
Exec dbms_flashback.enable_at_time (to_date ('2017-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 codeThe Code is as follows:
Select * from hr. job_history as of timestamp to_timestamp ('2017-07-23 10:20:00 ', 'yyyy-mm-dd hh24: mi: ss ');

This method is simple and easy to grasp. The function is the same as the above time as the time before your misoperation, preferably close to the misoperation, because the time limit for oracle data stored in the rollback persistence segment is determined by the value of the undo_retention parameter.

2. If you have deleted the database for a period of time, but you have a relatively new database backup, it will be restored through backup. Create a new database, restore the backup, export the table data, and then import it to the current database.

3. If the data has been deleted for a period of time and there is no backup, but the data is written to other associated tables at the same time, then try to write an SQL statement to get data from other tables and insert the data into the deleted table.

4. restore to the backup table
Copy codeThe Code is as follows:
Create table tableName_bak
As
Select * from tableName as of TIMESTAMP to_timestamp ('2018010101', 'yyyymmdd hh24mis ');