How to get OS PID for sessions which has KILLED status

All posts relating to Oracle database administration.

Moderator: Tim...

How to get OS PID for sessions which has KILLED status

Postby sivakumarocp » Tue Aug 14, 2012 7:40 pm

Hi Tim,

today I got a Critical incident from customer saying that they are not able to connect to the database, upon checking found Database reaches its max sessions limit and there were about 550 sessions were in INACTIVE status.

Code: Select all
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------
processes                            integer                          1000
sessions                             integer                          1105


so i killed all the sessions which has INACTIVE status after that its status changed to KILLED and it didn't go off from the Database. Meanwhile customer urging me to resolve the issue as this is a Tier 1 database. At this time I am not able to get session id and serial# for sessions which has KILLED status. Finally customer restarted their application server then KILLED sessions are removed from the database.

here my question is how can I get O/S process ID for a session which has KILLED status.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: How to get OS PID for sessions which has KILLED status

Postby Tim... » Tue Aug 14, 2012 8:22 pm

Hi.

Here's a tip,

"NEVER kill a session without first recording the at least the SID, SERIAL# and SPID!"

Once you have killed the session, there is no link to the SPID, so you've can't find the associated OS process. I I guess one option is to list all the process IDs for OS processes relating to Oracle connections and remove those that have existing sessions. The rest will be your half-killed processes.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: How to get OS PID for sessions which has KILLED status

Postby sivakumarocp » Tue Aug 14, 2012 8:35 pm

whether below query satisfy your answer.

Code: Select all
SELECT SPID FROM V$PROCESS WHERE NOT EXISTS (SELECT 1 FROM V$SESSION WHERE PADDR = ADDR);
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: How to get OS PID for sessions which has KILLED status

Postby Tim... » Tue Aug 14, 2012 8:54 pm

Hi.

You can try. I would be kinda surprised if the process records are still there if you've done a kill.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 3 guests