Search This Blog

Total Pageviews

Tuesday 14 October 2014

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

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')
;

Oracle DBA

anuj blog Archive