Show sql for users in the RAC database ..... and Kill session SQL  

show_sql.sql

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||'@'||INST_ID||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             '-- alter system kill session '||''''||sid||','||serial#||'@'||INST_ID||''''|| ' immediate ;' kill ,
             to_char(LOGON_TIME,' Day DD YYYY HH24:MI') logon_time,
             to_char(sysdate,' Day DD YYYY HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from gv$session
       where 1=1
          and  status != 'INACTIVE'
      --  and last_call_ET >1000
      --  and MACHINE='ANUJ'
      --  and username ='USER'
          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 gv$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.kill);
                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;
 

 ==============================

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||'@'||INST_ID|| ') ospid = ' ||  process ||
                ' program = ' || program username,
             '-- alter system kill session '||''''||sid||','||serial#||'@'||INST_ID||''''|| ' immediate ;' kill ,
             to_char(LOGON_TIME,' Day DD YYYY HH24:MI') logon_time,
             to_char(sysdate,' Day DD YYYY HH24:MI') current_time,
            event,
            sql_id,
             sql_address, LAST_CALL_ET,
             status
        from gv$session
       where 1=1
          and  status != 'INACTIVE'
      --  and last_call_ET >1000
      --  and MACHINE='ANUJ'
      --  and username ='USER'
          and  SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
          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 gv$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.kill);
                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( x.event );
                dbms_output.put_line( x.status );
                dbms_output.put_line( x.sql_id );
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
       end loop;
    end loop;
end;

output ....

-- alter system kill session '1474,47941@1' immediate ;
ANUJ(1474,47941@1) ospid = 25799 program = sqlplus@rac01
(TNS V1-V3)
Friday    23 2018 07:02  Wednesday 07 2018 07:41 last et = 1039108
resmgr:cpu quantum
ACTIVE
bp061zzjnstuu
BEGIN
        WHILE  1 = 1
        LOOP
                NULL;
        END
LOOP;
END;

============================================


set linesize 300 pagesize 300 
column username           format a30 word_wrapped
column module             format a30 word_wrapped
column action             format a15 word_wrapped
column client_info        format a30 word_wrapped
col kill 				for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  as kill,
 username,
       module,
       action,
       client_info,
      sql_id,
     prev_sql_id,
     event 
from gv$session s
where 1=1
and module||action||client_info is not null
and  schemaname is not null 
and  schemaname not in ('SYS')
;