Solution to Oracle errors after oracle data changes

Source: Internet
Author: User

Solution to Oracle errors after oracle data changes

In the process of using Oracle, we 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. Therefore, the password field is changed to 123456.

After the DMP backup file is created, several new users are created over a period of time, and other information of some old users has been changed. However, the password of the old user must be restored to the previous one, And the taw_rm_user table in the backup file must be imported to the com user directly. After analysis, a solution is provided. 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 key SQL statements used are as follows:

-- Create table comtest in the taw_rm_user table of the backup test database. taw_rm_user_bakasselect * from comtest. taw_rm_user -- restore the taw_rm_user.password field in the official database to declarerec_comtest comtest. taw_rm_user % rowtype; cursor cur_comtest isselect * from comtest. taw_rm_user; beginopen cur_comtest; loopfetch cur_comtest into rec_comtest; exit when cur_comtest % notfound; update com. taw_rm_userset com. taw_rm_user.password = rec_comtest.passwordwhere 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 without changing all other information.




Related Article

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.