DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: v$sql, v$sqlarea

  1. #1
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334

    v$sql, v$sqlarea

    Is there a way to join the sql_text in either of these to a user who executed them?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    hmm probably not cos the same statements are grouped together - oh well

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what do you mean join with user?

    v$session and v$sql?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    basically want to know who executed the sql that is stored in v$sql. The session will be over by now though

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    in v$session there are sql_address,sql_hash_value / prev_sql_addr,prev_hash_value columns pointing to the current/last statement executed; which you can find in v$sql.

    But I think logminer is what you need.
    Toma┼ż
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Feb 2003
    Posts
    40
    Try this to find which user is executing what sql statement

    select s.username ,st.sql_text from v$session s,v$sqltext st
    where s.sql_hash_value=st.hash_value
    and s.sql_address=st.address order by address,piece;

    Regards
    Chithra
    K.Chithra
    Oracle DBA

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    the session is gone and finihsed so I cannot use v$session. Cant use logminer either as its only selects they are doing

    oh well, out of luck

  8. #8
    Join Date
    Jun 2003
    Posts
    7
    for future reference try this sql

    select s.username, st.sql_text
    from v$session s, v$sql st, v$sqltext vs
    where
    s.sql_hash_value=vs.hash_value
    and s.sql_address=vs.address
    and st.command_type=vs.command_type;

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by Informix_guy
    for future reference try this sql

    select s.username, st.sql_text
    from v$session s, v$sql st, v$sqltext vs
    where
    s.sql_hash_value=vs.hash_value
    and s.sql_address=vs.address
    and st.command_type=vs.command_type;
    er may I ask what's the difference of that and

    select s.username ,st.sql_text from v$session s,v$sqltext st
    where s.sql_hash_value=st.hash_value
    and s.sql_address=st.address order by address,piece;


    other than it's slower???

  10. #10
    Join Date
    Jun 2003
    Posts
    7
    My query gives more complete sql text than yours....and yes it takes a little longer.

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