Solution to an error after changing the data in Oracle

Today, I made a serious mistake at work: I got rid of the data in the Oracle database used by our system! When I found myself correct, I suddenly burst into a cold sweat. However, if we do not back up the database on a regular basis and recover the database properly, there should be no major problems, because I used an update statement incorrectly.

The problem is as follows:

We have created two users for the system: com and comtest. The former stores the formal database data and the latter stores the test database data. They all have a table named taw_rm_user, which stores the basic user information of the system. I changed all the password fields to 123456.

The DMP backup file was created on July 11, September 28. After the seven-day long holiday, it has been around for more than a decade. I checked the log and found that several new users have been created, in addition, other information of some old users has been changed. Importing the taw_rm_user table in the backup file to a com user is definitely not acceptable. After a period of calm thinking, I got a solution. The general idea is:

1. Use the create table as statement to export the taw_rm_user TABLE of the test database to a temporary backup TABLE (the user TABLE in the test database cannot be changed randomly );

2. Delete the table taw_rm_user;

3. Run the IMP command to import the taw_rm_user table of the DMP backup file to comtest;

4. Compile a PL/SQL program block by yourself. Update the password field in the newly imported table to the corresponding record of the com. taw_rm_user table by using a cursor loop. Note: Only the corresponding records are updated here, and the newly added user records are not modified;

5. Restore the taw_rm_user table backed up to comtest.

The two key statements used are as follows:

-- Backup the taw_rm_user table in the Test Database

Create table comtest. taw_rm_user_bak

As

Select * from comtest. taw_rm_user

-- Restore the taw_rm_user.password field in the official database

Declare

Rec_comtest comtest. taw_rm_user % rowtype;

Cursor cur_comtest is

Select * from comtest. taw_rm_user;

Begin

Open cur_comtest;

Loop

Fetch cur_comtest into rec_comtest;

Exit when cur_comtest % notfound;

Update com. taw_rm_user

Set com. taw_rm_user.password = rec_comtest.password

Where com. taw_rm_user.user_id = rec_comtest.user_id;

End loop;

Close cur_comtest;

Commit;

End;

In this way, the passwords of all old users can be restored to the previous without changing all other information.