Spying on sessionsPosted: May 14, 2008
Sometimes I want to know what a session, a process, a user or a machine is doing, so I wrote a kitchen-sink query that shows me everything I want to know about what a session is running (10g and up).
Its amazing how many issues you can catch simply by running a simple query few times.
select --'alter system kill session '''|| s.SID||',' || s.serial# ||'''' , --'kill -9 ' || p.spid, p.SPID UnixProcess ,s.SID,s.serial#,s.USERNAME,s.COMMAND,s.MACHINE,s.SQL_ADDRESS,s.SQL_HASH_VALUE ,s.program, cpu_time,fetches,disk_reads,buffer_gets,rows_processed, executions,child_latch,event,sql_text,COMMAND_TYPE, sbc.name,to_char(sbc.last_captured,'yyyy-mm-dd hh24:mi:ss'),sbc.value_string from v$session s join v$process p on p.ADDR = s.PADDR-- and p.SID in (27817) left outer join v$sqlarea sa on sa.ADDRESS = s.SQL_ADDRESS left outer join V$SQL_BIND_CAPTURE sbc on sbc.ADDRESS = s.SQL_ADDRESS where 1=1 --and s.program like 'imp%' and s.username ='DEV' --AND p.SPID in (15179,8304) --and s.SID in (1019)
The fancy code formatting looks better when Don Seiler is using it. Maybe its time for a new look for the blog.