DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: History of SQL statements for a current user

  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Unhappy

    In OEM 2.1 diagnostics pack, I recall in TOPSESSIONS you could see a list of a users previous SQL statements and do explain plans on them. In the 9i version all I seem to be able to see is the current statement.

    Is it possible in either TOPSESSIONS or basic SQL to see such a history.

    I only care about connected sessions, and I would like to see an order history of their statements not even going too far back, a few minutes at most.

    regards, John.

  2. #2
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Cool

    Since you are using 9i and OEM you can use LogMiner Viewer to get this information.

    Configure LogMiner with either redo logs or online data dictionary support, and use the GUI to get this info.

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our web site: Here


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    v$sql

    logminer is to see DML/DDL not queries

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    V$sql keep only sql statments without information about session ID

    Probably u can see only current and previous sql statment only one) for session id
    if u have to use v$sql + v$session something like:

    select vs.sid,
    s1.sql_text current_stmt,
    s2.sql_text previous_stmt
    from v$session vs,
    v$sql s1,
    v$sql s2
    where vs.SQL_ADDRESS = s1.ADDRESS and
    vs.PREV_SQL_ADDR = s2.ADDRESS
    and
    s1.ADDRESS <> s2.ADDRESS union
    select vs.sid,
    s1.sql_text current_stmt,
    '*' previous_stmt
    from v$session vs,
    v$sql s1
    where vs.SQL_ADDRESS = s1.ADDRESS and
    vs.PREV_SQL_ADDR = vs.SQL_ADDRESS
    /

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