sqlplus scott/tiger@tnsname hangs
- From: Prasad <p4cldba@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Apr 2008 21:30:42 -0700
Greetings.
This was something that happened today in one of the database and I am
looking for list's
response on this. This is a Oracle 9.2.0.7 database running solaris 9.
Around 10am the Unix group called me and told me that one of the osprocess
is taking more cputime
then what it normally takes. and upon investigation I found that this
database was running on
shared server mode with the osprocess being the only dispatcher at that
time . So I created another
3 dispatcher and stopped the existing dispatcher d000.
and everything was going fine until 2.30pm when the user called me and told
me that he is not able to
make connection using servicename. so I did a test
test1- sqlplus '/as sysdba'
test2- sqlplus scott/tiger
test3- sqlplus scott/tiger@tnsname
I was able to logon to database successfully with test1 and test2 . However
when I did test3
it just hangs and it didnt allow me to interrupt it also.
I checked if the dispatchers were busy or not and didnt found anything
significant.
The listener logging was not enabled so the initial reaction was to shutdown
and start the
listener which I did and at this time the test3 worked fine . However the
user was still not able to
access its application and I observed a large number of session in
dba_blocker and dba_waiter
and also few deadlock error in alert log file . So I killed the session on
the beginning of the queue
and it immediately releases all the sessions in dba_blockers and dba_waiters
and the user was
able to access the application.
Here comes the hard part what is the root cause of this issue?
Please advise.
Thanks
-Prasad
Other related posts:
