All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
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,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) 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 || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
Output:
--------------------
APPS(1798,52456) ospid = 27275 program = JDBC Thin Client
Tuesday   23:55  Saturday  00:57 SQL#=2974764904
BEGIN WF_EVENT_OJMSTEXT_QH.DEQUEUE(:1, :2, :3); END;
--------------------
APPS(1802,29631) ospid = 25442 program = JDBC Thin Client
Tuesday   17:32  Saturday  00:57 SQL#=3007600688
BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8); END;
--------------------
APPS(1869,62) ospid = 25454 program = JDBC Thin Client
Tuesday   17:32  Saturday  00:57 SQL#=252511374
BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;
--------------------
APPS(1910,51) ospid = 25464 program = JDBC Thin Client
Tuesday   17:32  Saturday  00:57 SQL#=3007600688
BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8); END;
--------------------
APPS(1928,46269) ospid = 25454 program = JDBC Thin Client
Tuesday   17:32  Saturday  00:57 SQL#=3007600688
BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8); END;
--------------------
APPS(1940,23223) ospid = 27277 program = JDBC Thin Client
Tuesday   23:54  Saturday  00:57 SQL#=252511374
BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;
--------------------
APPS(1963,56120) ospid = 25464 program = JDBC Thin Client
Tuesday   17:32  Saturday  00:57 SQL#=252511374
BEGIN FND_CP_GSM_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;
Elapsed: 00:00:03.76