Quick Response after Oracle accidentally deletes table data

1. query and modify the undo_retention parameter: run the showparameterundo command to view the undo_retention settings of the database at that time. Shown as follows: undo _

1. query and modify the undo_retention parameter: run the show parameter undo command to view the current database parameter undo_retention settings. Shown as follows: undo _

1. query and modify the undo_retention parameter:

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

Shown 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:

Alter system set undo_retention = 10800 SCOPE = BOTH;

Ii. Quick Reply after Oracle accidentally deletes table data:

[Method 1 ]:
With the flash back feature provided by oracle:
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;
In this way, the deleted data can be returned to the corresponding table. First, ensure that the user has the permission to execute the dbms_flashback package.

Method 2 ]:
Insert into hr. job_history
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.