List of sql statements for a session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: List of sql statements for a session

  1. #1
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    Hello Friends,
    I have recentely joined as member in this forum, even thouh i am a regular guest. This is my first query.

    I would like to know all the sql statements executed in a particular active session. And also what is the difference in V$sql, V$sqlarea views.

    Thanks in Advance.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hello

    Welcome to a great forum .Enjoy your stay here.i bet it would be a great learning experience.and now to ur answer

    V$SQLAREA first 80 characters of the sql statements

    V$SQLTEXT full sql text

    to know all the sql statement executed in particular session use thsi query

    select nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')'||DECODE(Ses.AUDSID,userenv('SESSIONID'),'**','') UNAM,
    SQL.SQL_TEXT 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.USERNAME is not null
    and SES.SQL_ADDRESS = SQL.ADDRESS
    and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
    and ses.sid = &&Session_ID
    order by sql.piece

    you could also first search thsi forum before posting a query.who knows your question might have laready been answered.

    regards
    Hrishy


  3. #3
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    Hi Hrishy
    Thanks for ur reply.
    This query given by u is giving the latest query executed in that session. Suppose i have executed 5 queries in a session, i want to see all the 5 sql statements executed. can i see this from any table/view. Will audting help in this, if so how can i do it.

    Thank u.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cant, you can only see the last 2 sql stmts

  5. #5
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    Dear Pando

    Thank u.
    how can see atleast the 2 latest sql statements. Can u throw some light on this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width