Error after Oracle Data Change

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.