1

I have a .dmp file I received from a third party, and I'm trying to load it into Oracle SQL Developer. I don't have much information on how this file was extracted, but if I knew what info I needed, I could go back and ask them.

Since I'm a complete Oracle newbie, I've been trying to run this through the Data Pump Import Wizard, but it's coming back with errors instead of loading. At first, we had this:

>Processing object type SCHEMA_EXPORT/USER
>ORA-39083: Object type USER failed to create with error:
>ORA-02380: profile NEEDED_USER does not exist

It then had this error all the way down the log for every item. I googled this, and found that I needed to create the profile in the database. Perfect! Easy! I went ahead and did that in SQL Plus, then ran the file through again and got this error message:

>Processing object type SCHEMA_EXPORT/USER
>ORA-39083: Object type USER failed to create with error:
>ORA-00959: tablespace 'DATA' does not exist

This is where I stopped, because creating a tablescape seems more complicated and it also seems like I'd end up playing whack-a-mole creating these items one-by-one, and I know that there are a LOT of tables in there. When I googled using the Data Pump Import Wizard, I wasn't able to find this specific problem.

Is it possible that there's an issue with the dmp file itself? Any guidance on what questions I should ask of the originator of the file, or a way I should request them to extract it? Also, is it possible I set up the database incorrectly on my end - again, I'm a complete newbie and am trying to muddle through as best I can, so it won't surprise me if I made mistakes. Any help would be greatly appreciated! Thank you!

L. Moore
  • 11
  • 1
  • 2
  • 1
    This is what you need https://stackoverflow.com/a/6708618/1156452 – thatjeffsmith Jun 15 '18 at 18:50
  • 1
    Depending on how closely you want to match the original DB you might want to create everything reported as missing (see that link); but if not there are options like [remap_tablespace](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/datapump-import-utility.html#GUID-F467946E-50B3-4CC0-937A-CC8C18AAF8C1) to modify things as they are imported. Soinds like you need to spend some time exploring the documentation *8-) – Alex Poole Jun 15 '18 at 19:04
  • I'd suggest doing whack-a-mole by remapping any missing tablespaces to an existing one, e.g.: `REMAP_TABLESPACE=(DATA:USERS,DATA2:USERS,DATA3:USERS)` – kfinity Jun 15 '18 at 19:14

1 Answers1

1

Error: I got below error while import

Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER:"ABCD" failed to create with error:
ORA-02380: profile ABC_PROFILE does not exist

Failing sql is:
 CREATE USER "ABCD" IDENTIFIED BY VALUES 'S:D6A96B73ECA93542E03C3479' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "DATA" PROFILE "ABC_PROFILE"

Solution: Step-1 Create a profile before import.

CREATE PROFILE "ABC_PROFILE" LIMIT IDLE_TIME 15

Step-2 Remap table space name on imp job

remap_tablespace=XYZD_DATA:ABCD_DATA
remap_tablespace=XYZD_INDX:ABCD_INDX

Step-3 use below imp.par file vi imp.par

userid=system/password
dumpfile=ABCD%U.dmp
directory=dum_dir
logfile=ABCD_imp.log
parallel=8
cluster=N
transform=OID:n
job_name=abcdimp
remap_schema=XYZD:ABCD
remap_tablespace=XYZD_DATA:ABCD_DATA
remap_tablespace=XYZD_INDX:ABCD_INDX

Step-4 Then start import job again

nohup impdp parfile=imp.par &
Srikant Patra
  • 399
  • 4
  • 5