Hi Krish, There is no logon triggers on this datbase. and also this database was running with 1 dispatcher and i increased it to 4 so it should not maxed out. the listener logging was not enabled so i could not get a clear picture on this issue. thanks Prasad On Thu, Apr 17, 2008 at 8:13 PM, <krish.hariharan@xxxxxxxxxxxx> wrote: > Prasad, > > > > A long shot, and perhaps as a process of elimination: > > > > 1. Do you have any logon triggers (I am not sure personally, if the > logon trigger fires before acknowledgement it sent back to the client) > 2. As perhaps a consequence, I wonder if you reached the max > sessions per dispatchers and max dispatchers and got blocked > > > > Again a longshot given that things cleared up when you killed the session > in the beginning of the queue > > > > -Krish > > > ------------------------------ > > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Prasad > *Sent:* Thursday, April 17, 2008 5:57 AM > *To:* David cheyne > *Cc:* ORACLE-L > *Subject:* Re: sqlplus scott/tiger@tnsname hangs > > > > Hi David/Jack, > > Listener Logging was switched off on this database . This is a solaris 9 > server . > > Thanks > Prasad > > On Thu, Apr 17, 2008 at 3:09 AM, David cheyne <david.cheyne@xxxxxxxxx> > wrote: > > Prasad, > > > > You say that the listener log was not enabled. Have you switched off the > logging or was it not updating? You don't say which O/S your on, but you > can easily automate its management. > > > > Without bringing the listener down on Unix/Linux: > > > > cp listener.log listener.OLD > > cat /dev/null > listener.log > > > This copies the log to another name and blanks the current log. You just > may need to watch disk space if your keeping multiple copies (tar/zip?) > > > > > > > > David Cheyne > > > > > > On 17/04/2008, *Jack van Zanen* <jack@xxxxxxxxxxxx> wrote: > > maybe check the size of your listener.log file > > > > I have seen occasions where a large log file saw this behaviour > > > > Jack > > > > On 17/04/2008, *Prasad* <p4cldba@xxxxxxxxx> wrote: > > 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 > > > > -- > J.A. van Zanen > > > > -- > ~~~~~~~~~~~~~~~~~~~~~~~ > David Cheyne > BA(hons.) > Oracle Database Administrator > > -- Outside of a dog, a book is a man's best friend. Inside of a dog it's > too dark to read. > Groucho Marx > > >