runaway process in oracle/unix

[jlakhani]

I usually run top on my sun unix box to see whats running and how much load there is on the box. I find manytimes i get a process in oraclet or oraclep which has taken 15minutes of cpu and a cpu load of 45%. How to i figure why what that process is doing and which user is running that process in oracle.?
running:
ORacle 8i with 11i apps. unix

thanks
Jigar

[marist89]

select s.sid, s.serial#, p.spid server_pid, s.username, s.program, s.server, s.status
from v$session s, v$process p
where p.addr = s.paddr
order by to_number(p.spid)

In the above query, "server_pid" is the process id from ps -ef

[jlakhani]

Thanks but what i need to know is what is that process doing and is it a runaway process or dead process? I would like to look at the sql of that process as well if possible

thanks

[marist89]

select s.username, s.terminal, s.program, sa.sql_text
from v$session s, v$sql_area sa, v$process p
where s.paddr = s.addr
and a.sql_address = sa.address

[sysdba]

I think you meant V$sqlarea instead of V$sql_area

[jlakhani]

Jeff i dont think your sql is correct
select s.username, s.terminal, s.program, sa.sql_text
from v$session s, v$sql_area sa, v$process p
where s.paddr = s.sql_address
and sa.address = sa.address

i modified this sql to run but i freezes up on me

[dragon99]

Try this:
rem this is ps_view.sql
col username format a15
col osuer format a10
col program format a20
set verify off
select a.username, a.osuser, a.program, spid, sid, a.serial#
from v$session a, v$process b
where a.paddr = b.addr
and spid = '&pid';

rem this is ps_sql.sql
set verify off
column username format a15
column sql_text format a60
undefine sid
undefine serial#
accept sid prompt 'sid: '
accept serial prompt 'serial#: '
select 'SQL Currently Executing: '
from dual;

select b.username, a.sql_text
from v$sql a, v$session b
where b.sql_address = a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';

select 'Open Cursors: '
from dual;

select b.username 'USERNAME', a.sql_text 'SQL_TEXT'
from v$open_cursor a, v$session b
where b.sql_address =a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';

Note: pid comes from top command.
Any problem, let me know.

[jlakhani]

Run all this together? in a shell script? or sqlplus?

Jigar

[jlakhani]

hello,
i didnt get an anwer for my question, shall i just take the script you gave me and run it all together.??