Home > SQL*Plus > Explicit connection to a database without using tnsnames

Explicit connection to a database without using tnsnames

Time for a quick “did you know..?

You can connect to an Oracle Database using SQLPLUS without the need for a tnsnames.ora file using the following syntax:

sqlplus sys/password@//server:1521/orcl as sysdba

Advertisements
Categories: SQL*Plus
  1. ebrian
    March 14, 2008 at 7:58 pm

    As I mentioned, the ORACLE_SID is set (albeit being set with the registry setting). If you have another database on your Windoze box, then the ORACLE_SID in the registry isn’t going to do any good for the other databases.

  2. Bob Windsor
    March 14, 2008 at 6:50 pm

    I am a local machine, and Guido was right. I am on a Windows machine. When I went into
    \HKEY_LOCAL_MACHINE\Software\Oracle I found that ORACLE_SID was set.

    I even ran a little test:
    I unset the Oracle_SID
    SET ORACLE_SID=”

    C:\>sqlplus scott/tiger

    SQL*Plus: Release 10.2.0.3.0 – Production on Fri Mar 14 10:20:41 2008
    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    ERROR:
    ORA-12560: TNS:protocol adapter error
    and poof – I couldn’t connect anymore without the @dbname

    C:\>sqlplus scott/tiger

    SQL*Plus: Release 10.2.0.3.0 – Production on Fri Mar 14 10:20:41 2008
    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    ERROR:
    ORA-12560: TNS:protocol adapter error

    I reset the SID and my connect worked again. I will have to review what ORACLE_SID is telling who, when.

    C:\>set ORACLE_SID=dbname

    C:\>sqlplus scott/tiger

    SQL*Plus: Release 10.2.0.3.0 – Production on Fri Mar 14 10:22:31 2008
    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Connected to: (etc.)

    Thank you for the help. It is better to have some idea what I’m doing.

  3. ebrian
    March 14, 2008 at 3:22 am

    Bob,
    That’s because you either have the ORACLE_SID set and are using a bequeath connection, or if you are remote, you must have the TWO_TASK env variable set and a viable tnsnames entry.

  4. Guido Zeelen
    March 13, 2008 at 11:16 pm

    Bob,

    Your host is on a Windows platform. That means there is a LOCAL setting somewhere in the registry \HKEY_LOCAL_MACHINE\Software\Oracle, defining the SID.

  5. Bob Windsor
    March 13, 2008 at 10:41 pm

    I understand that you can connect using the description as in ebrian’s example – but I have been connecting without it.
    i.e.
    sqlplus scott/tiger

    And I don’t remember why that works.

  6. ebrian
    March 13, 2008 at 8:55 pm

    You can also connect without a tnsnames file using:

    sqlplus scott/tiger@”(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP_or_host)(PORT= 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=orcl)))”

  7. Bob Windsor
    March 13, 2008 at 6:48 pm

    When connecting to oracle from the host – my test database – I use
    ‘C:\>sqlplus scott/pwd’. I once knew why this works – but I don’t remember and haven’t found the
    explanation. Something about connecting directly from the server. Can someone state it coherently for me?
    Thanks

  8. March 13, 2008 at 5:07 pm

    You may want to be more specific since I think this syntax (known as EZConnect if I remember correctly) is only available with sqlplus >= v10.2. 9i clients won’t be able to use this syntax (and there are still a LOT of 9i clients out in the world).

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: