How do you show SQL executing on an Oracle database?

  • How can you show the SQL that is currently executing on an oracle db?

    Extra information that would be useful would include user, session id etc.

  • David Mann

    David Mann Correct answer

    9 years ago

    Most of the info is available in v$session ... and the SQL Text can be grabbed from v$sql or v$sqltext_with_newlines...

    Here is a query that I often use that formats In-flight SQL, longest running at top.

    -- In Flight SQL 
    SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
           SID,   
           MACHINE, 
           REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
          ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
           || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
           || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
      FROM V$SESSION SES,   
           V$SQLtext_with_newlines SQL 
     where SES.STATUS = 'ACTIVE'
       and SES.USERNAME is not null
       and SES.SQL_ADDRESS    = SQL.ADDRESS 
       and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
       and Ses.AUDSID <> userenv('SESSIONID') 
     order by runt desc, 1,sql.piece;
    

    what is the meaning of In Flight SQL?

    In-flight = currently running on an oracle database, or in this case currently available in v$session and status='ACTIVE'

  • Good answer found here.

    Execute the following sql:

    select x.sid
          ,x.serial#
          ,x.username
          ,x.sql_id
          ,x.sql_child_number
          ,optimizer_mode
          ,hash_value
          ,address
          ,sql_text
    from   v$sqlarea sqlarea
          ,v$session x
    where  x.sql_hash_value = sqlarea.hash_value
    and    x.sql_address    = sqlarea.address
    and    x.username       is not null;
    

    If the output is unreadable, change the LINESIZE (take from here):

    SET LINESIZE 20000
    

    If the sql above does not work, you might need to log in as sysdba:

    sqlplus '/as sysdba'
    

    Your link to the Good answer is dead

    @Gerrat I updated the link to a cached archive.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM