Hello,
I have Oracle 11g set up in my personal system and it is not allowing me to connect to DB when I give user id/password along with SID. It allows me to connect when not giving the SID. For ex

1. connecting with SID (orcl )
------------------------
C:\Users\ctsuser1>sqlplus calypso/****@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 13:56:41 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.
-------------------
the command prompt hangs at this point and no response comes even if I wait for more than 5-6 hours and I have to ultimately kill the session by pressing Ctrl+C.

2. without SID
---------------
C:\Users\ctsuser1>sqlplus calypso/****

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 30 14:14:16 2012

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


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

SQL>
-----------------

I would like to know what is the reason of not able to connect to db without specifying SID.

I don't want to install another DB as the current DB with SID (ORCL) already has 3-4 users on which I have some important data placed.

Regards,
Ramakant

You are mistaken & misguided!

>sqlplus calypso/****@orcl
above uses SQL*Net to connect to the DB & does NOT use environmental variable ORACLE_SID

>sqlplus calypso/****
above uses the environmental variable ORACLE_SID

so what are the actual ORACLE_SID values for all the involved databases?

Hello,
Thanks for the details. Please note that I was able to connect to DB by using the SID till 3-4 days ago and got this problem only around this time. I did some google to see on how to set the ORACLE_SID and tried the following

1. set ORACLE_SID=orcl

from the same command window, I executed the sqlplus command giving the DBname as is step 1 in the first thread

connecting with SID (orcl )
------------------------
C:\Users\ctsuser1>sqlplus calypso/****@orcl


and am not getting anything.

Following is my TNS entries
------------------
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.cts.com)
)
)
----------------