Connect to oracle database without tns entry

by · Published · Updated

Oracle Database

Many times we have requirement of connecting database to execute a sql script but we sometimes dont find the tnsnames.ora file and sometimes we dont have permission to edit.
So to avoid this issue we can connect to oracle database without tns entry.

For Windows:
sqlplus "<USERNAME>/<PASSWORD>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<FQDN/HOSTNAME>)(PORT=<LISTEN_PORT>)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<SID>)))"
For *NIX:
sqlplus '<USERNAME>/<PASSWORD>@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<FQDN/HOSTNAME>)(PORT=<LISTEN_PORT>)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<SID>)))'

Example:

Linux:
sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ccdb.techpaste.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TP22DB)))'
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Apr 29 23:49:34 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows:

sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ccdb.techpaste.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TP22DB)))"
SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 30 12:40:56 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.

Tags:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.