Solution to Error after Oracle Data Change

In the process of using Oracle, we created two users for the system: com and comtest. The former stores formal library data, while the latter stores test library data. They all have an taw_rm_user table, which stores the basic information of users who use the system, so all the password fields in it are changed to 123456.

After the DMP backup file was created, several new users were created over a period of time, and other information about some old users has changed. However, it is necessary to restore the password of the old user to the past, and it is definitely not possible to directly import the taw_rm_user table in the backup file into the com user. After analysis, a solution is obtained. The general idea is:

1. Import the taw_rm_user table of the test library into a temporary backup table with CREATE TABLE AS statement (the user table in the test library can not be changed indiscriminately);
2. Delete the taw_rm_user table again;
3. Then import the taw_rm_user table of the DMP backup file into comtest with IMP command;
4. Write another PL/SQL block and update the password field in the newly imported table to the corresponding record in the com.taw_rm_user table by using a cursor loop. (Note that only the corresponding records are updated here, and no modification is made to the newly added user records);
5. Restore the previously backed-up taw_rm_user table to comtest.

The key SQL statements used are as follows:

--  Back up the in the test library taw_rm_user Table 
create table comtest.taw_rm_user_bak
as
select * from comtest.taw_rm_user
--  Restore the in the official library taw_rm_user.password Field 
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;

This allows you to restore the passwords of all old users without changing all other information.