Home → 2005/01/25, 16h33

Oracle EXP

When trying to use the Oracle client EXP program to export an entire database from an Oracle remote DB server, I lost a lot of time because of my misunderstanding of a very basic concept in Oracle:

  1. the file network\ADMIN\TNSNames.ORA and
  2. what is the SERVICE_NAME.

The file in #1 contains all the databases that are accessible using the installed Oracle client. This file typically gets populated during the installation of the client package by sniffing on the network.

In this file, you have many blocks that look like this: 


  MYDB.WORLD =
   (DESCRIPTION =
    (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = db.domainOrIP.com)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = db.domainOrIP.com)(PORT = 1526))
    )
   (CONNECT_DATA = (SID = MYDB))
  )

To run the EXP command, you open a DOS command, and simply type EXP. Note that the oracle/Ora81/bin must be in your system PATH for the command to work simply like this. But this is not a DOS crash course so...

Once in EXP, you get asked to enter a username. Based on the sample above, the username should be: username@MYDB.WORLD

where username is the database username you want to export. After that, you enter its password.

The problem I had was that I specified the host name of the DB server -- db.domainOrIP.com -- and the error I got was: 


EXP-00056: ORACLE error 12514 encountered
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor
EXP-00000: Export terminated unsuccessfully

Then I thought I should add the SID from the ora file like this: username@db.domainOrIP.com/MYDB.WORLD. But I got the same error. This is the type of thing that makes you look very bad in a newsgroup, or a mailing list. Oracle users take all of this as granted as copy/paste (if you read this, you must be familiar with this copy/paste concept. If you are not, call me at home mom).

There many other reasons why you can get errors while trying to export tables using EXP. I found a whole bunch of potential causes in my searches. None of them was the cause. The cause was simply a bad usage of the EXP command. Let's hope this simple explanation will help seldom Oracle users like me that want to export, or import all tables of a database.

This was in Oracle 9 but may have also been the same on other versions like 8. Or is it 8i and 9i? What does "i" mean anyway...? And now, it's 10g!