How to connect sqlplus without tnsnames.ora


I always prefer to use TNSNAMES.ORA to connect to my database server. It is one of the most convenient way for the database servers who HOST Name or IP is fixed. If TNSNAMES.ORA has following entry
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ngarg.mydomain.co.in)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

TNS looks into "TNSNAMES.ORA" file to find out the meaning of "orcl" and connects Oracle Database Server Machine "ngarg.mydomain.co.in" on port 1521 "orcl" service when we fire following command like
C:\Users\nimish.garg>sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 30 14:24:03 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


There are times when we do not want to modify our TNSNAMES.ORA may be because we might want to connect a particular database service only for one time. So what are the other way possible to Connect to Oracle Database without using TNSNAMES.ORA? There are two ways for this:

1) EZCONNECT

EZCONNECT is Oracle's easy connect naming method. EZCONNECT eliminates the need for service name lookups in TNSNAMES.ORA files when connecting to an Oracle database across a TCP/IP network.

Syntax of EZCONNECT:
sqlplus username/password@[//]host[:port][/service_name]

Example:
C:\Users\nimish.garg>sqlplus scott/tiger@ngarg.mydomain.co.in:1521/orcl
SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 30 14:36:00 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL>

To enable EZCONNECT "sqlnet.ora" should have naming methods "ezconnect" specified in NAMES.DIRECTORY_PATH Parameter. "sqlnet.ora" is located in $ORACLE_HOME/network/admin
Example:
NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)


2) TNS Connect String
The TNS Connect String also known as Connect Descriptor is a type of connect identifier. It defines the parameters that need the Oracle Net Service to connect to a database service

Syntax of TNS Connect String
sqlplus "username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=servicename)))"

in above syntax lower case letters must be replaced with the actual values. Please remember there should not be any space in the connect string, also no carriage return and in UNIX put the single quote instead of double quote.

Example
C:\Users\nimish.garg>sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ngarg.mydomain.co.in)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=orcl)))"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 30 15:05:13 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL>

I hope you all have enjoyed reading this article. Please provide your valuable comments :)



Related Posts
- SQLNET: How does Oracle Client connect with Oracle Server
- Query optimization tips for Oracle
- SQL Interview Question Answers
- ORA-01017: invalid username/password; logon denied
- How to configure Case Sensitive Password in Oracle

13 comments:

  1. Logon with the TNS connect string syntax does allow spaces in windows and in unix.

    ReplyDelete
  2. Hello There. I found your blog using msn. This is an extremely well written article.
    I will be sure to bookmark it and come back to read more
    of your useful information. Thanks for the post. I will certainly comeback.

    ReplyDelete
  3. It's hard to come by experienced people on this subject, however, you ound like you know what you're
    talking about! Thanks

    ReplyDelete
  4. Get Instant help from our expert for your Database issues or to Optimize your SQL Script. We will be charging on a hourly basis. You can initiate the chat with our expert for the best and fast solution.

    Site URL: https://sqloptimize.com

    ReplyDelete
  5. Thanks! I am not allowed to edit tnsnames.ora, so this post saved me !!

    ReplyDelete
  6. Very Useful. thanks for putting it together!

    ReplyDelete

  7. INCREASE EFFICIENCY
    Genex Database experts work together with your team to monitor, supervise, support and improve database infrastructure.
    Remote Database Support



    IMPROVE AVAILABILITY
    Genex DB offers 24#7 service coverage to improve availability of critical database infrastructure.


    INCREASE RESOURCE POOL
    With Genex DBA support, you do not need to worry about staff sick days, vacations, absences and employee turnover.Remote Database Support

    ReplyDelete
  8. QloudHost is a highly respected web hosting provider, known for offering the best DMCA Ignored Hosting services. Their focus on quality, reliability, and success makes them the ideal choice for offshore website hosting. With QloudHost, you can be assured that your website will always be online and running smoothly. Trust QloudHost for the best DMCA Ignored Hosting services available.

    ReplyDelete
  9. pg ดีที่สุดเกมออนไลน์ PG SLOT สล็อตบนโทรศัพท์เคลื่อนที่ แบบใหม่ตอนนี้ ของโลก สมัครเล่น SLOT วันนี้รับโบนัส แรกเข้า 100% ในทันทีทันใด โบนัส 50% สำหรับสมาชิกใหม่

    ReplyDelete