This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt. This tip was written with a little help from Oracle Metalink.
set pause off set pause 'Press Enter to continue.......' break on sid skip 1 set feedback off set verify OFF column sid format 99999 column sql_text form a500 prompt Please enter [Session ID] SID to trace.... accept p_sid_number prompt prompt Showing running sql statements ........................... prompt column last_call_et heading "Last Called|Time" Column event format a30 Column sid format 9999 Column session_cpu heading "CPU|used" Column physical_reads heading "physical|reads" Column consistent_gets heading "logical|reads" Column seconds_in_wait heading "seconds|waiting" select a.sid Current_SID, a.last_call_et ,b.sql_text from v$session a ,v$sqltext b where a.sid = &p_sid_number and a.username is not null and a.status = 'ACTIVE' and a.sql_address = b.address order by a.last_call_et,a.sid,b.piece; prompt prompt Showing what sql statement is doing..................... prompt select a.sid, a.value session_cpu, c.physical_reads, c.consistent_gets,d.event, d.seconds_in_wait from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d where a.sid= &p_sid_number and b.name = 'CPU used by this session' and a.statistic# = b.statistic# and a.sid=c.sid and a.sid=d.sid;
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.