Shulou(Shulou.com)05/31 Report--

How to recover after Oracle deleted table data by mistake, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

How to recover quickly after mistakenly deleting table data by Oracle

1. Query and modify undo_retention parameters:

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

The display is 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 are seconds. That's three hours.

Modify the default undo_retention parameter settings:

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH

How to recover quickly after mistakenly deleting table data by Oracle

2. The method of quick reply after mistakenly deleted table data by oracle:

Method 1:

Through the flashback function provided by oracle:

Exec dbms_flashback.enable_at_time (to_date ('2007-07-23 10-15-21-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12, respectively, exec dbms_flashback.enable_at_time (' 2007-07-23 10 hh34:mi:ss' 21 hh34: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 (rhombotemp.EMPLOYEE ID repertoire ritual temp. JOBZ ID repertory rhombotemp. STARTATDATErereachable temp. Endpoint date)

Commit

END LOOP

CLOSE c_temp

END

This method can restore the deleted data to the corresponding table. The first step is to ensure that the user has the right to execute the dbms_flashback package.

Method 2:

Insert into hr.job_history

Select * from hr.job_history as of timestamp to_timestamp ('2007-07-23 10 yyyy-mm-dd hh34:mi:ss' 20 yyyy-mm-dd hh34:mi:ss')

This method is simple and easy to master, and the function is the same as the time before your misoperation, preferably close to the misoperation.

This is the answer to the question about how to recover the table data after Oracle deleted the table data by mistake. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.