Re: help on killing sessions in oracle

From: Andrey N. Kalinin <NDY_at_itco.msk.su>
Date: 1996/01/17
Message-ID: <4djbmh$k9c_at_news.demos.su>#1/1

urass_at_urmama.com (thx1179) wrote:

>I could use some help and/or advice on killing sessions in Oracle 7.1.
<Skipped>

Try this script.
I'm using it on Oracle 7.1.6.2.0 on AIX 4.1. (Be sure procedural option installed).

• Cut Here ------------------------------ -- -- Use in order to killing any session in Oracle -- Run this script under SYSTEM --

SET ECHO OFF
SET VERIFY OFF
PROMPT Active sessions:

select sid, username, status
from v$session
where NOT status = 'KILLED' AND

      username is not null;

ACCEPT uinp NUMBER PROMPT 'Enter <SID> of session to kill (Ctrl-C - exit):'

SET TERMOUT OFF VARIABLE result CHAR(60)

DECLARE

   csr  NUMBER;
   ser# NUMBER;
   rtn  NUMBER;
   cursor sess is
      SELECT serial#
      FROM v$session
      WHERE sid = &&uinp AND
            NOT status = 'KILLED' AND
            username IS NOT NULL;

BEGIN
   OPEN sess;
   FETCH sess INTO ser#;
   IF sess%NOTFOUND THEN

      CLOSE sess;
      :result := 'Where is no session with specified ID!';
   ELSE
      CLOSE sess;
      csr := dbms_sql.open_cursor;
      dbms_sql.parse(csr,'alter system kill session ''&&uinp,' || ser#
|| '''',
                     DBMS_SQL.V7);
      rtn := dbms_sql.execute(csr);
      dbms_sql.close_cursor(csr);
      :result := 'Session killed.';

   END IF;
EXCEPTION
   WHEN OTHERS THEN
      IF dbms_sql.is_open(csr) THEN dbms_sql.close_cursor(csr); END IF;

      IF sess%ISOPEN THEN CLOSE sess; END IF;
      :result := 'WHERE IS AN ERROR WHEN TRYING TO KILL SESSION!';

END; / SET TERMOUT ON PRINT result SET VERIFY ON

• Cut Here ----------------------

I hope this helps.

Regards.

---

                                     Andrey N. Kalinin
 Software Development Department, Information Technologies Co.,
              Russia, 117218 Moscow, p.o. box 116.

 phones: +7(095) 124 5833;+7(095) 127 9010 fax: +7(095) 129 1275  Email: NDY_at_itco.msk.su

---

Received on Wed Jan 17 1996 - 00:00:00 CET