Connaitre les requêtes exécutées par session sous Oracle

Afin de diagnostiquer des problèmes, il peut être opportun de connaître les requêtes exécutés par une session. Voici un exemple tiré de ce site http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:497421739750 :

COLUMN STATUS format a10
SET feedback off
SET serveroutput ON
 
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;
/

Merci à Drazzib pour l'astuce.