6

My Setup

I'm running Windows 7 64-bit, using Oracle DB 11g R2 for Windows 64-bit. I have Oracle SQL Developer Release 3.0 (4.0.2.15.21) as the GUI for my database.

Question

I followed the instructions from this post on how to import an Oracle .DMP file. I issued this command into the command prompt: C:\>imp sys@dev/password12345 FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp full=yes

It then prompts me for a password in which I enter the correct one and then I receive this error:

IMP-00058: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve the connect identifier specified
IMP-00000: Import terminated unsuccessfully

Actual Question: How do I fix this error and be able to import the .dmp file successfully? Or what are some steps I can head towards to potentially get this working...?

My Files & What I've Done

I have looked through countless posts on this same issue. My tnsnames.ora, listener.ora, & sqlnet.ora are located in this directory: C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN

My connection in my tnsnames.ora file looks like this (I have also tried to put my computer name as the HOST, instead of 127.0.0.1)...

DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DEV)
  )
 )

PATH Variables

I added the following to my PATH (I realize there may be some unnecessary item in there, I just wanted to be on the safe side. ): C:\oracle\product\11.2.0\dbhome\bin;C:\oracle\product\11.2.0\client_1;C:\oracle\product\11.2.0\client_1\bin;%ORACLE_HOME%\bin;

I also created two more variables in my System Variables...

TNS_ADMIN C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN

TNS_NAMES C:\oracle\product\11.2.0\dbhome\NETWORK\ADMIN

Improve this question
  • 2
    sys@dev/password12345 is not a proper specification. sys/password12345@dev is the right one. Don't use sys for export and import. – miracle173 Aug 19 '14 at 18:54
  • @miracle173 Using sys/password12345@dev, I got this error connection as SYS should be as SYSDBA or SYSOPERUsername: SYSDBA ... but are you saying that using sys in general will not work? – james Aug 19 '14 at 19:02
  • The following work for me from windows command prompt: imp userid=<user-name>/<password>@<oracle-server-name>:<port>/<service-name> file=<file-name-with-path.dmp> FULL=Y Port: Usually 1521 Service Name: Usually ORCL – Azhar Mateen Dec 6 '19 at 17:26
6

Use this on Windows (three nested double quotes):

exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes

imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp

In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent dump file silently. So it's better to avoid it, for example use system.

Improve this answer
  • I had to get rid of the full=yes, but other than that it worked! It would give me this error when I had full=yes ... IMP-00024: Only one mode (TABLES, FROMUSER, or FULL) can be specified – james Aug 19 '14 at 19:41
  • True, I haven't noticed it :) Edited my answer for the sake of future readers. – kubanczyk Aug 19 '14 at 19:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.