RE: Which SQL is executing

From: Rajesh Dayal <Rajesh_at_ohitelecom.com>
Date: Wed, 06 Jun 2001 00:57:29 -0700
Message-ID: <F001.0031D758.20010606002037@fatcity.com>

<SPAN

class=477195906-06062001>Hi Chuan,
<SPAN

class=477195906-06062001> 
<SPAN

class=477195906-06062001>    Still your question is not clear to me. But I am
<SPAN

class=477195906-06062001>sending you some scripts, may be these would help you:
<SPAN

class=477195906-06062001> 
<SPAN

class=477195906-06062001>This gives SQL-TEXT, Oracle-User-name, machine-name
<SPAN

class=477195906-06062001>and program-name for the user executing the SQLs.
<SPAN

class=477195906-06062001> 
<SPAN

class=477195906-06062001>set pagesize 50
<SPAN

class=477195906-06062001>set head on
<SPAN

class=477195906-06062001>set linesize 70
<SPAN

class=477195906-06062001> 
<SPAN

class=477195906-06062001><FONT color=#0000ff face="Lucida Console" size=2>select sa.sql_text,s.username,s.machine,s.program from v$sqlarea sa,v$session s where s.sql_address=sa.address and s.sql_hash_value=sa.hash_value order by sa.buffer_gets desc/
 
<SPAN

class=477195906-06062001>This will give all SQLs currently executing including
<SPAN

class=477195906-06062001>this <FONT color=#0000ff face="Lucida Console" size=2><SPAN
class=477195906-06062001>one.
<SPAN

class=477195906-06062001> 
<SPAN

class=477195906-06062001>SELECT u.sid, SUBSTR(u.username,1,12) user_name, s.sql_textFROM v$sql s, v$session uWHERE s.hash_value = u.sql_hash_valueORDER BY user_name/
<SPAN

class=477195906-06062001>HTH,
<SPAN

class=477195906-06062001>Rajesh

  <FONT face=Tahoma
  size=2>-----Original Message-----From: root_at_fatcity.com   [mailto:root_at_fatcity.com]On Behalf Of Chuan ZhangSent:   Wednesday, June 06, 2001 11:25 AMTo: Multiple recipients of list   ORACLE-LSubject: Re: Which SQL is executing   Hi, Sam,

   
   Thanks for your information. 
   

  To my understanding, the script you gave is   for all the queries. There is still no way to figure out which query is   running.
   
  Chuan,
  <BLOCKQUOTE
  style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Sam Roberts To: <A href="mailto:ORACLE-L_at_fatcity.com" title=ORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L Sent: Monday, June 04, 2001 5:00 PM Subject: Re: Which SQL is executing

    SELECT T.SQL_TEXT FROM V$SQLTEXT T,V$SESSION S      WHERE  S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE;      
    Sam
    <BLOCKQUOTE
    style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Chuan Zhang To: <A href="mailto:ORACLE-L_at_fatcity.com" title=ORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L Sent: Monday, June 04, 2001 9:30 AM Subject: Which SQL is executing

      Hi All,
       
       
      From v$open_cursor, I know every SQL  
      opened and parsed in one session. Is there any way to know which SQL is 
      running. Or put another way, can all the SQLs in one session be 
      sorted in timing order dynamically?
       
      Any clue would be much 
      appreciated.
       
      <FONT face=Arial 

size=2>Chuan Received on Wed Jun 06 2001 - 02:57:29 CDT