Oracle DBA

"Let the users kill their own oracle sessions"  -  tesztelve Oracle 10g-ben

mivel egyre tobb ilyen jellegu kerest kapok mostanaban (please, kill this or that session...)

ugy dontottem megadom a lehetoseget a kollegaknak, hogy sajat maguk lojek ki a "nemkivanatos" munkameneteket.

ez nem trivialis, mivel alter system rendszerjogosultsag szuksegeltetik, azt meg ugye nem osztjuk szire-szora :)

Fontos kiemelni, hogy csak a sajat felhasznalo altal folyamatban levo munkameneteket kepesek a kollegak kiloni (magyarul, ha user scott futtat egy monster lekerdezest, akkor pl.: sqlplus-ba be kell lepjen scott es csak igy tudja kiloni a masik sessiont, ahol a lekerdezese fut).

Telepitese: egy poweruser schema-jaba (<POWERUSER>) az alabbi pkg-t installaljuk.

a kovetkezo jogosultsagot szuksegesek az installalo (<POWERUSER>) schemanak (direktben grantolva, role-on keresztul nem mukodik):

grant select on sys.v_$session to <POWERUSER>;

grant alter system to <POWERUSER>;

majd johet a pkg krealasa.

Telepites utan (<POWERUSER> felhasznalokent):

grant execute on <POWERUSER>.cs_util to public;
create public synonym cs_util for <POWERUSER>.cs_util;

Tovabba:

- SYS% session-t nem lehet kiloni

- mivel nalunk a prod adatbazisok neve PZH.... ezert a P% instance-okon nem lehet hasznalni a pkg-t (kis elovigyazatossag)

 CREATE OR REPLACE PACKAGE CS_UTIL AS

  PROCEDURE KillSid(in_sid IN NUMBER, in_serial in number);

END CS_UTIL;
/
CREATE OR REPLACE PACKAGE BODY cs_util
AS
   PROCEDURE killsid (in_sid IN NUMBER, in_serial IN NUMBER)
   AS
      suser      VARCHAR2 (30);
      killuser   VARCHAR2 (30);
   BEGIN
      SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')
        INTO suser
        FROM DUAL;

      SELECT username
        INTO killuser
        FROM SYS.v_$session
       WHERE SID = in_sid AND serial# = in_serial;

      IF killuser <> suser
      THEN
         raise_application_error
                (-20001,
                 'SID owner and user executing this procedure does not match'
                );
      ELSIF SUBSTR (SYS_CONTEXT ('USERENV', 'INSTANCE_NAME'), 1, 1) = 'P'
      THEN
         raise_application_error
                 (-20003,
                  'Killing sessions are not allowed on production instances.'
                 );
      ELSIF suser LIKE 'SYS%'
      THEN
         raise_application_error (-20004,
                                  'Killing SYS% processes are not allowed.'
                                 );
      ELSE
         EXECUTE IMMEDIATE    'alter system kill session '''
                           || in_sid
                           || ','
                           || in_serial
                           || '''';
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error (-20002,
                                     'SID: '
                                  || in_sid
                                  || ', serial: '
                                  || in_serial
                                  || ' does not exist.'
                                 );
   END;
END;
/