TNS less connection in Oracle

Few dbapool users have asked me about TNS less connection in Oracle. I have put together this article for them and everybody else who are seeking for this information.

I have tested this connection method in Oracle 8i, 9i and 10g. I have not tested it in 11g but I hope it should work.

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 15 23:04:00 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect

scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=testsrv)(Port=1521))(CONNECT_DATA=(SID=TEST)))

Connected.

SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g, you can use following method as well. This method does not work for 8i or 9i databases.

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 15 23:04:00 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> scott/tiger@testsrv:1521/TEST

Connected.

SQL