How Can I import or open a .dmp file?

Karan Neginhal picture Karan Neginhal · Feb 15, 2018 · Viewed 13.7k times · Source

Update:

I tried the impdp command and it's giving me that it cannot create a user. I tried creating the user as well

This is how my .par file looks like

This is a snip of .sh file

enter image description here

I have never used the oracle database before. I have a .dmp file which is 50 GB. I don't know how it was exported or which version it was exported from. I downloaded Oracle 12c release 2 and tried to do an import but I get the error ".dmp may be a Data Pump export dump file". What do I need to do so that I can run SQL queries on it eventually? Please see the attached image.

UPDATE : I tried the command : IMP SYSTEM/Password SHOW=Y FILE=DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp fromuser=SYSTEM touser=SYSTEM

It gave me a message saying import terminated successfully with warnings. what does this do? Also, where can I view the data now if it's imported?

Answer

Younes El-karama picture Younes El-karama · Feb 15, 2018

in sqlplus as SYSTEM:

CREATE DIRECTORY IMPDIR as 'C:\Users\negink\Documents\databasewrigley';

back in command line:

impdp SYSTEM/Password DUMPFILE=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp logfile=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.log FULL=Y

when done, you can remove the DIRECTORY object

in a CDB database (which is your case), this will not work, unless you pre-create all the users and roles in SQLPLUS, after running this command:

alter session set "_ORACLE_SCRIPT"=true;
create user x identified by pwdx;
create user y identified by pwdy;
create role r1;
create role r2;
...

Otherwize, you can create a PDB inside your CDB and import your DMP file into the PDB. In this case, you'll need to modify the connection in the IMPDP command as follows (change SYSTEM/Password to SYSTEM/Password@//localhost/pdb_name) :

impdp SYSTEM/Password@//localhost/pdb_name DUMPFILE=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp logfile=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.log FULL=Y