Gaining the UNIX Process ID (PID) based on Oracle SID and Serial#

Posted: May 19, 2014 in UNIX Command Line and SQL PLUS
Tags: ,

Happy Monday, Oracle enthusiasts!

My Monday started with an email asking me to look at a ten minute process had been running since Saturday. Right off the bat, before I finished my morning cup of coffee, I needed to kill a process that has gone rogue.

  1. I first talked to production support and looked at the log to discover any information I could about the process. I determined the process owner or username.
  2. I next checked my OEM 12c tool that I love to rave about. I found no active jobs or processes with this username.
  3. Next, I queried v$session for the user. I found two ‘INACTIVE’ processes, one of which was the rogue process. According to production support it was active, so could I please kill it.  Time to open SQL Plus and run…
  5. Great.  Now the status of the session went from INACTIVE to KILLED. This, of course, does not mean the session is dead. It is only “MOSTLY DEAD” (quoted from Billy Crystal in The Princess Bride). So we wait until the query finishes rolling back the transaction. Since the process has been inactive since Saturday afternoon, this rollback process could be awhile.
  6. After talking to the developer in charge of the process, it was determined that no rollback was needed. So I determined to kill the job from the UNIX process id. So how does one determine this? Back to the SQL prompt…
  7. SQL> SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine, p.terminal, s.program
    FROM v$session s, v$process p
    WHERE s.paddr = p.addr
    and s.sid = rogue_sid;
  8. server01:/home/oracle(db_sid)> kill -9 spid       --where spid is the unix process id you want to kill.

Sounds easy, right? In the past I have killed a job based on SID and SERIAL# and assumed the job was dead when it was only “MOSTLY DEAD”. This caused a blocking session that turned out to be a mess to kill. If you are going to let Oracle do the kill, run your query until the SID and SERIAL# disappear from v$session.

Questions?  Comments?

Have a great afternoon!







Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s