Finding the Oracle session based on a UNIX/Linux process

Often you’ll find a rogue Oracle process tearing up the CPU and need to know what it is. The following is a simple script to do that for you, given the PID of the OS process.

set long 10000
set pages 50 linesize 120
set verify off

accept os_pid prompt 'Enter the OS PID of the process: '

prompt Checking for a dedicated server connection
select p.spid, s.sid, s.username, s.status,
       nvl(sql.sql_fulltext,'<no active SQL>') sql_text
from v$session s, v$process p, v$sql sql
where p.addr = s.paddr
and s.sql_id = sql.sql_id(+)
and s.sql_child_number = sql.child_number(+)
and p.spid = &os_pid
/

prompt Checking for a shared server connection
select p.spid, ss.name, ss.status, s.sid, s.username,
              nvl(sql.sql_fulltext,'<no active SQL>') sql_text
from v$process p, v$shared_server ss, v$circuit c, v$session s, v$sql sql
where p.addr = ss.paddr
and ss.circuit = c.circuit(+)
and c.saddr = s.saddr(+)
and s.sql_id = sql.sql_id(+)
and s.sql_child_number = sql.child_number(+)
and p.spid = &os_pid
/

undef os_id

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

set long 10000

set pages 50 linesize 120

set verify off

accept os_pid prompt ‘Enter the OS PID of the process: ‘

prompt Checking for a dedicated server connection

select p.spid, s.sid, s.username, s.status,

       nvl(sql.sql_fulltext,'<no active SQL>’) sql_text

from v$session s, v$process p, v$sql sql

where p.addr = s.paddr

and s.sql_id = sql.sql_id(+)

and s.sql_child_number = sql.child_number(+)

and p.spid = &os_pid

/

prompt Checking for a shared server connection

select p.spid, ss.name, ss.status, s.sid, s.username,

              nvl(sql.sql_fulltext,'<no active SQL>’) sql_text

from v$process p, v$shared_server ss, v$circuit c, v$session s, v$sql sql

where p.addr = ss.paddr

and ss.circuit = c.circuit(+)

and c.saddr = s.saddr(+)

and s.sql_id = sql.sql_id(+)

and s.sql_child_number = sql.child_number(+)

and p.spid = &os_pid

/

undef os_id

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Times New Roman”,”serif”;}

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s