Poznate to..<\/p>\n
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.<\/p>\n
riesenie?: funkcia killsid. procedura a funkcia\u00a0 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:<\/p>\n
——————– 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 … Continue reading ORACLE: sessions<\/span> grant select on sys.v_$session to ops$oracle;
\ngrant select on sys.v_$mystat to ops$oracle;
\n<\/code>
\n——————<\/p>\n
\nCREATE TABLE \"OPS$ORACLE\".\"KILLSID_LOG\"
\n(\u00a0\u00a0\u00a0 \"KTO\" VARCHAR2(30 BYTE),
\n\"ODKIAL\" VARCHAR2(30 BYTE),
\n\"SID_OWNER\" VARCHAR2(20 BYTE),
\n\"SCHEMANAME\" VARCHAR2(20 BYTE),
\n\"SID\" NUMBER,
\n\"KEDY\" VARCHAR2(50 BYTE)
\n);<\/code><\/p>\ncreate or replace FUNCTION\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"OPS$ORACLE\".\"kill_sid\" (
\nSID_TO_KILL IN NUMBER
\n)
\nRETURN VARCHAR2<\/code><\/p>\nAS
\nPRAGMA AUTONOMOUS_TRANSACTION;
\nUSR varchar(50);
\nPROC_OWNER varchar(50);
\nPROC_STAT varchar(50);
\nSNUMBER integer;
\nqueryna varchar(2000);
\nlog_schemaname varchar(2000);
\nlog_machine varchar(2000);
\ndatum varchar(50);<\/code><\/p>\nbegin<\/code><\/p>\nSELECT USERNAME into USR FROM sys.v_$session WHERE sid = ( SELECT sid FROM sys.v_$mystat WHERE rownum = 1);
\nselect 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;
\nSELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') into datum FROM dual;
\nif (sql%found) then
\ndbms_output.put_line('My username:' || USR);
\ndbms_output.put_line('SID OWNER:' || PROC_OWNER);
\ndbms_output.put_line('SID STAT:' || PROC_STAT);
\ndbms_output.put_line('SNUMBER:' || SNUMBER);
\nDBMS_OUTPUT.ENABLE;
\n<\/code>
\nexecute 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;
\ncommit;
\nif USR <> PROC_OWNER then
\nRAISE_APPLICATION_ERROR (-20000,'nemozem zabit session ktora nieje tvoja!');
\nelse
\nqueryna:='Alter system kill session ''' || SID_TO_KILL|| ',' || SNUMBER||'''';
\ndbms_output.put_line(queryna);
\nexecute immediate queryna;
\nreturn 'ok,killed';
\nend if;
\nend if;<\/code><\/p>\nEXCEPTION when NO_DATA_FOUND then return 'cant find this SID';
\nend;<\/code><\/p>\n
\ngrant alter system to OPS$ORACLE;
\nCREATE PUBLIC SYNONYM KILL_SID FOR \"OPS$ORACLE\".\"kill_sid\";<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"