Is it normal to see "USER already exists" with REMAP_DATA in Data Pump import?

[sarahwang]

Hi experts,

I use version 17.1.717.3711.
I'm trying to explore the feature of the DATA PUMP parameter REMAP_DATA, and I've made it work finally.
However, I'd like to double-check with you to see if there's anything I might be missing or can improve.

Purpose:
I want to export data from schema HR999, and import it into a new schema HR12345,
but with one modification: the salary column values in the employees table should be multiplied by 0.01 during the import process.

Here’s what I did:
1.Used SYS to create the user HR12345.

2.Logged in as HR12345 and created a package called remap_pkg which includes the remap_salary_func function that multiplies salary by 0.01.

3.Exported the HR999 schema using Data Pump.

4.Performed the import (as HR999) with the following REMAP_DATA parameter:
hr999.employees.salary:hr12345.remap_pkg.remap_salary_func

5.Verified that the salary values in HR12345.employees were successfully updated to 1% of the original.

Now I have a few questions:

Q1. During the import, I got this message:ORA-31684: Object type USER:"HR12345" already exists.
The import still completed successfully, but is there any way to avoid this warning?

Q2. This is my first time using the REMAP_DATA parameter and I followed Oracle's documentation.
Based on the steps above, is there anything I could do better or more efficiently?

Q3. I noticed that Data Pump Export in Toad also has a "Remap Functions" field.
Does REMAP_DATA also work during export, or is it import-only?

[Andy_Young]

A1. You probably did not do a DROP USER HR12345 because you want to keep stuff about that user. As far as I know, there's no way to suppress the warning.

A2. That code looks good. I've had varying results with the performance of REMAP_DATA versus doing a SQL UPDATE after the import. In Oracle 11g REMAP_DATA was faster but in Oracle 19c UPDATE is faster. I think it has something to do with DIRECT PATH vs EXTRTNAL TABLE.

A3. Yes, Both. Oracle Data Pump Export

[sarahwang]

Hi Andy,

Thank you for your reply and tips.
Regarding Q2 and Q3, I will give them a try later.

Since this is my first time using the REMAP_DATA parameter, I’d like to confirm Q1 a bit more.

(1) If I don’t create the user HR12345 beforehand, I guess I won’t be able to use REMAP_DATA, right? (as shown below)

(2) If I want to successfully use REMAP_DATA, I guess I need to create the user HR12345 beforehand.
It seems there’s no way to avoid the warning "ORA-31684: Object type USER:"HR12345" already exists", right?

[Andy_Young]

(1) Correct
(2) Correct

[sarahwang]

Hi Andy,

Thanks for your reply — it really gave me some confidence!
I’d been trying for a while before I finally got it to work.