Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Veiwing or killing procedures

Re: Veiwing or killing procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 Jan 1999 14:01:39 GMT
Message-ID: <36b21bcb.2337070@192.86.155.100>


A copy of this was sent to "Ilya Joel-Pitcher" <ilya_at_solsup.com.au> (if that email address didn't require changing) On Wed, 27 Jan 1999 16:34:04 +1100, you wrote:

>Is there a way to determine whcih procedure may be running on the server and
>selevtively kill them if they are called through OWS?
>
>Thanks
>
>Ilya
>

something like this, when run in sqlplus, will show you that:


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

begin

    for x in
    ( select username||'('||sid||','||serial#||') ospid = ' || process || ' pro gram = ' || program username,

     to_char(LOGON_TIME,' Day HH24:MI') logon_time,
     to_char(sysdate,' Day HH24:MI') current_time,
             sql_address
        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);
                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 is not null;

part of the output will look like:



INTRANET(94,30963) ospid = 4283 program = ? @aria (TNS V1-V3) Wednesday 08:57 Wednesday 08:57
SELECT * FROM QUICK_PEOPLE_TV WHERE ROWID IN (SELECT ROW_ID FROM FAST_EMPLOYEES WHERE NAME LIKE '%' || UPPER(:b1) || '%' )ORDER BY LAST_NAME,NVL(KNOWN_AS,FIRST_NAME)

that shows the session user (INTRANET), the sid and serial# for that session (94,30963) some other stuff and what they are executing. If you wanted to, you could issue:

SQL> alter system kill session '94,30963';

to kill it.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 27 1999 - 08:01:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US