Home » RDBMS Server » Server Administration » Wait event still prevailing (Oracle Database 10.1.0.2.0,HP-UX B.11.23)
Wait event still prevailing [message #405658] Thu, 28 May 2009 23:20 Go to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
i Have killed a session through database on 20-may-09 but that session is still waiting on wait event,it is being 9 days the wait event is their, i am not able to find the solution to kill it from os prompt as no pid or spid is there

SID SERIAL# USERNAME EVENT STATE BLOCKING_SESSION
523 4 abcSQL*Net break/reset to client

WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT
Application 0 545,287

please provide me the solution that why is this happening and how to clean it.Thnx in advance

Report message to a moderator

Re: Wait event still prevailing [message #405659 is a reply to message #405658] Thu, 28 May 2009 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the status of the session?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 28 May 2009 23:22]

Report message to a moderator

Re: Wait event still prevailing [message #405660 is a reply to message #405658] Thu, 28 May 2009 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>please provide me the solution that why is this happening and how to clean it.
Oracle can not/will not eliminate (internal) session unless & until the external OS process get eliminated.

kill -9 <pid>

Report message to a moderator

Re: Wait event still prevailing [message #405661 is a reply to message #405659] Thu, 28 May 2009 23:28 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Status is showing 'killed'.

Report message to a moderator

Re: Wait event still prevailing [message #405662 is a reply to message #405658] Thu, 28 May 2009 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Status is showing 'killed'.
The actual text depends upon version.
Oracle can not/will not eliminate (internal) session unless & until the external OS process get eliminated.

Report message to a moderator

Re: Wait event still prevailing [message #405663 is a reply to message #405660] Thu, 28 May 2009 23:33 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
>Oracle can not/will not eliminate (internal) session unless & until the external OS process get eliminated.

>kill -9 <pid>

i knw that but i can't find pid or spid because when i using the query
select p.pid,p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid=523
the result is 'no row selected'

Report message to a moderator

Re: Wait event still prevailing [message #405665 is a reply to message #405658] Thu, 28 May 2009 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>i knw that but i can't find pid or spid
You should have thought of that before READY, FIRE, AIM trying to kill session.
If/when the OS process is killed first, nothing needs to be done in DB.

If you note session/process start time, you can identify the OS process.

Report message to a moderator

Re: Wait event still prevailing [message #405668 is a reply to message #405665] Thu, 28 May 2009 23:50 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
No i just know the user logon_time(V$session) nothing much more than that,still is their any possiblity to find the pid.

Report message to a moderator

Re: Wait event still prevailing [message #405669 is a reply to message #405658] Thu, 28 May 2009 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
login as root
init 6

Report message to a moderator

Re: Wait event still prevailing [message #405671 is a reply to message #405669] Thu, 28 May 2009 23:59 Go to previous messageGo to next message
vinniora
Messages: 56
Registered: October 2008
Location: Mumbai
Member
Thanx for the solution but i can't fire init6 as database is 24x7

Report message to a moderator

Re: Wait event still prevailing [message #405672 is a reply to message #405658] Fri, 29 May 2009 00:02 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> i just know the user logon_time(V$session)
compare to

ps -ef

Report message to a moderator

Previous Topic: Move RAC database - need to format the disk storage having RAC and ASM installed
Next Topic: ORA-01034: ORACLE not available, ORA-27101: shared memory realm does not exist followed by ORA-01017
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Jul 02 21:56:14 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.