sqlplus connections-old school

This blog was posted to be reminder that sqlplus connection may be initiated in different ways.

Core files

Content of tnsnames.ora file:

HACN.WORLD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=volt.teb-ing.hr)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SID=HACT)
    )
  )

tnsping checking:

c:\Documents and Settings\damir>tnsping hacn

TNS Ping Utility for 32-bit Windows: Version 10.2.0.5.0 - Production on 30-SVI-2012 15:20:28

Copyright (c) 1997,  2010, Oracle.  All rights reserved.

Used parameter files:
d:\Users\DamirV\Documents\teb\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=volt.teb-ing.hr) (PORT=1521)) (CONNECT_DATA= (SID=HACT))) OK (40 msec)

Ordinary tnsnames connection string

Most common way to connect to some database by using entries from tnsnames.ora file:

c:\Documents and Settings\damir>sqlplus tab/passd@hacn

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:20:01 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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


TAB@hacn>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Simulated tnsnames connection string

If you do not want to use tnsnames.ora definitions, you might simulate them, by entering values directly in command:

c:\Documents and Settings\damir>sqlplus tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:29:13 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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


TAB@HACT>

If you use some special characters, for Windows env put whole string in double quotes:

sqlplus "tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))"

and for Linux env put in single quotes (and place escape character if needed):

sqlplus 'tab/passd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=volt.teb-ing.hr)(PORT=1521))(CONNECT_DATA=(SID=HACT)))'

Easy_connect_method

This is not too known but also regular method to connect without tnsnames.ora entry:

c:\Documents and Settings\damir>sqlplus tab/passd@//volt.teb-ing.hr:1521/HACT

SQL*Plus: Release 10.2.0.5.0 - Production on Sri Svi 30 15:21:05 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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

TAB@//volt.teb-ing.hr:1521/HACT>

In all examples I use $ORACLE_HOME\sqlplus\admin\glogin.sql file custom definition:

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>"

If there is any other method, please let me know. Cheers!