Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Unanswered: v$sql AND v$session info for Inactive sessions

    Hi All,
    V$sql gives us the statistics for the SQL executed , we can join V$session and
    v$sql , which will give sqls
    which are being currently executing along with their session values. Once they are done(means their status comes
    to inactive), sql_address in v$session will be '00' so if will be difficult to get the session info(who executed that sql) .

    Is there any way by which i can track a SQL (say a truncate command) back to its Session info even after the user has logged out and that session has been terminated.

    I am in Oracle 9i .

    Thanks in advance!!


  2. #2
    Join Date
    May 2004
    BA [ARG]
    Are you using SQL_ADDRESS on v$session?

    Because I think you can also use prev_sql_addr with a decode like this:
    address = decode(sql_hash_value, 0, prev_sql_addr, sql_address)

  3. #3
    Join Date
    Mar 2002
    Reading, UK
    You cant get info on the session after the session has been terminated as their is no record of the session in v$session. What you can do though is to log ddl using a ddl trigger and get then session info at the time of the ddl. I use this on our production databases so that we have a record of who did what ddl (we also log all database errors using an error trigger). However if you cant use this method for DML (i.e. who is changing what data).


Posting Permissions

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