Solution to Oracle errors after oracle data changes

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.