Poznate to..

Vo svete IT sa casto vyskytuju situacie, ked nieco (aplikacia, skript, query) nebezi tak ako by podla ocakavani mala..a je to potrebne killnut. Vo vacsine pripadov to nieje ziadna extra velka issue..v OS Windows je mozne zabit svoje procesy a procesy ktore by ma mohli zauimat, prekticky bez problemov, v unixovych prostrediach tiez..ale v databazovych prostrediach je (pokial nechcete dat kazdemu userovi privilegium na alter system) trochu problem. DBA su v principe lenivy ludia a to posledne, co chcu je dat privilegia ktore su schopne rozhasit databazu uzivatelom ale tiez nechcu, aby sa nikto bez nich nezaobysiel.

riesenie?: funkcia killsid. procedura a funkcia  moze mat dva mody operacie a to dedit privilegia toho, kto ju spusti alebo dedit privilegia schemy v ktorej je zapisana. ak teda zapisem funkciu do systemovej schemy ktora ma grant na alter system, pridelim jej public synonymum a dam uzivatelom na nu pravo na select, dorobim do nej pre istotu nejake logovanie a checky ze kazdy moze zostrelit len vlastnu session prakticky mam po probleme. a voila: hotovy skript:

grant select on sys.v_$session to ops$oracle;
grant select on sys.v_$mystat to ops$oracle;

——————


CREATE TABLE "OPS$ORACLE"."KILLSID_LOG"
(    "KTO" VARCHAR2(30 BYTE),
"ODKIAL" VARCHAR2(30 BYTE),
"SID_OWNER" VARCHAR2(20 BYTE),
"SCHEMANAME" VARCHAR2(20 BYTE),
"SID" NUMBER,
"KEDY" VARCHAR2(50 BYTE)
);

create or replace FUNCTION          "OPS$ORACLE"."kill_sid" (
SID_TO_KILL IN NUMBER
)
RETURN VARCHAR2

AS
PRAGMA AUTONOMOUS_TRANSACTION;
USR varchar(50);
PROC_OWNER varchar(50);
PROC_STAT varchar(50);
SNUMBER integer;
queryna varchar(2000);
log_schemaname varchar(2000);
log_machine varchar(2000);
datum varchar(50);

begin

SELECT USERNAME into USR FROM sys.v_$session WHERE sid = ( SELECT sid FROM sys.v_$mystat WHERE rownum = 1);
select USERNAME,STATUS,SERIAL#, machine, schemaname into PROC_OWNER,PROC_STAT,SNUMBER, log_machine, log_schemaname from sys.v_$session where sid=SID_TO_KILL and serial#!=1 and username!=' ' and rownum = 1;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') into datum FROM dual;
if (sql%found) then
dbms_output.put_line('My username:' || USR);
dbms_output.put_line('SID OWNER:' || PROC_OWNER);
dbms_output.put_line('SID STAT:' || PROC_STAT);
dbms_output.put_line('SNUMBER:' || SNUMBER);
DBMS_OUTPUT.ENABLE;

execute immediate 'insert into KILLSID_LOG (SID,KTO,ODKIAL,SID_OWNER,SCHEMANAME,KEDY) values (:1,:2,:3,:4,:5,:6)' using SID_TO_KILL,USR,log_machine,PROC_OWNER,log_schemaname,datum;
commit;
if USR <> PROC_OWNER then
RAISE_APPLICATION_ERROR (-20000,'nemozem zabit session ktora nieje tvoja!');
else
queryna:='Alter system kill session ''' || SID_TO_KILL|| ',' || SNUMBER||'''';
dbms_output.put_line(queryna);
execute immediate queryna;
return 'ok,killed';
end if;
end if;

EXCEPTION when NO_DATA_FOUND then return 'cant find this SID';
end;

——————–
grant alter system to OPS$ORACLE;
CREATE PUBLIC SYNONYM KILL_SID FOR "OPS$ORACLE"."kill_sid";