9

I want to be able to run expdp and impdp by directly specifying the connection parameters instead of a TNS name that resides in tnsnames.ora. Is this possible?

zb226
  • 9,586
  • 6
  • 49
  • 79
Shravan
  • 223
  • 1
  • 5
  • 12

2 Answers2

10

You may be able to specify it all on the command line with a Connection String instead of a TNSName. Remove the whitespace from your TNS entry you would have used to connect, here is an example that works with SQLPLUS...

Here is how to connect to a local OracleXE install on Windows:

sqlplus scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=xe)))

On *Nix you may need to quote the entry so the parens aren't interpreted incorrectly:

sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=xe)))'
zb226
  • 9,586
  • 6
  • 49
  • 79
David Mann
  • 1,960
  • 15
  • 15
  • 6
    While this works as given for `sqlplus`, it does not seem to work for `expdp` - I'm getting `LRM-00116: syntax error at 'ADDRESS_LIST' following '('` using Oracle 12.1.0.2.0. – zb226 Apr 12 '18 at 12:39
5

You might try an EZCONNECT string:

expdp userid=user/pw@//host:port/service-name

You will need a sqlnet.ora parameter on the client side that includes EZCONNECT, e.g.:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
DCookie
  • 42,630
  • 11
  • 83
  • 92
  • This works great! Note that the `//` is optional. Also, I didn't need to configure anything on the client side for it to work. – zb226 Apr 12 '18 at 15:17