Oracle: Using SQLPLUS and SQLLDR on databases without TNSNAMES entry
June 12, 2012 Leave a comment
There are circumstances when you would like to connect to databases with tools like SQLPLUS or SQLLDR which are not registerred in TNSNAMES.ora.
For example when you have no local admin rights to change the TNSNAMES.ora or you want to connect to databases you only rarely need, or just when you want to test a connection.
It works quite straight forward, you just have to put the same data as in the TNSNAMES entry into the connection string, although its a bit different in SQLLDR and SQLPLUS – that all was done and tested under Windows, but should be very similar on any other OS with an Oracle client:
For SQLPLUS, you can do like that (for example, executing “myscript.sql” on SID “MYDBSID” on database host “myDBHost”):
sqlplus -L username/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myDBHost)(PORT= 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDBSID)))" "@myscript.sql"
For SQLLDR, it’s quite the same, but you have to escape all quotes and brackets, so it looks a bit more ugly.
Importing mytable.ldr to SID “MYDBSID” on database host “myDBHost”:
sqlldr userid=username/password@\"\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=myDBHost\)\(PORT= 1521\)\)\)\(CONNECT_DATA=\(SERVER=DEDICATED\)\(SID=MYDBSID\)\)\)\" Control='mytable.ldr' Log='mytable.log'