Tuesday, February 08, 2005

A: Recently executed sql statement and user

well, i did a quick google search because my brain just would not pull the answer out of the recesses...here's the first hit my search came back with: this came from Ask Tom
this is the link to the forum discussion "How can I track the execution of PL/SQL and SQL?", version 8.1.6

---------------- showsql.sql --------------------------
column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                               sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
            then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
       module,
       action,
       client_info
from v$session
where module||action||client_info is not null;
there were plenty of comments and follow-ups on this discussion...i recommend reading the entire thread...you will find tons of valuable information

No comments: