wa'alaikum salam,
saya cuplik aja procedur di backend.
buat procedure ini sebagai user SYS.
grant execute ke user schedulernya
trus tinggal panggil aja.....
selama ini sih it runs well
procedure sys.pr_kill_session(p_sid in varchar2, p_serial# in varchar2) is
cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
select count(*) into ignore
from V$session
where username = 'nama_schemanya'
and sid = p_sid
and serial# = p_serial# ;
if ( ignore = 1 ) then
dbms_sql.parse(cursor_name,'alter system kill session '''
||p_sid||','||p_serial#||'''',dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
else
raise_application_error( -20001, 'You do not own session ''' ||
p_sid || ',' || p_serial# ||'''' );
end if;
END;
hth
--J
----- Original Message -----
> assalamu'alaikum wr wb
>
> selamat pagi kawan warga milis Indo Oracle.
>
> saya menggunakan oracle 9i,
> username admin aplikasi = adminapp
> username sys = sys
> username client = opr... (... mulai dari 1 s/d 147)
> yg diijinkan menggunakan plsql adalah username adminapp dan sys.
> setiap waktu selalu dimonitor user mana aja yg pakai plsql dg sript
> sbb:
> ------------------------
> SELECT s.sid SID, s.serial# SERIAL, s.username, RTRIM(s.osuser)
> OS_USER, s.machine, b.spid OS_ID, s.program,
> s.logon_time, s.status, s.resource_consumer_group
> FROM v$session s, v$session_longops l, v$process b, dba_resumable r
> WHERE (s.sid = l.sid(+))
> AND s.program in
> ('PLSQLDEV.EXE', 'PLSQLDev.exe', 'plsqldev.exe', 'crw32.exe')
> AND NOT s.username in ('adminapp', 'SYS')
> AND (s.sid = r.session_id(+))
> AND s.paddr=b.addr
> GROUP BY s.username, s.serial#, r.session_id, r.error_number,
> r.status, s.sid, s.status, s.osuser,
> b.spid, s.machine,
> s.program,s.logon_time,s.resource_consumer_group;
> -----------------------
>
> ternyata hasil dari run sql menunjukkan ada 2 user iseng. jika
> secara manual bisa dilakukan dg SQL
> -----------------------
> ALTER SYSTEM KILL SESSION '238,18067';
> -----------------------
> maka perintah tsb menghapus sid= 238 dan serial# =18067
>
> pertanyaannya adalah sbb:
> 1. bagaimanakah cara menghapus (dg script secara auto) semua user
> iseng yg menggunakan plsql.
> 2. maunya ada script yg bisa autorun setiap 10 menit.
> 3. berikut sript yg telah dibuat tetapi masih ada error tepat di
> ALTER SYSTEM KILL SESSION 'rec_.sid, rec_.serial';
> lengkapnya dibawah ini
> ----------------------
> SET SERVEROUTPUT ON;
> -- bila ada dbms_output.put_line(...)
> DECLARE
> CURSOR get_kill is
> SELECT s.sid SID, s.serial# SERIAL, s.username, RTRIM(s.osuser)
> OS_USER, s.machine, b.spid OS_ID, s.program,
> s.logon_time, s.status, s.resource_consumer_group
> FROM v$session s, v$session_longops l, v$process b, dba_resumable r
> WHERE (s.sid = l.sid(+))
> AND s.program in
> ('PLSQLDEV.EXE', 'PLSQLDev.exe', 'plsqldev.exe', 'crw32.exe')
> AND NOT s.username in ('IFSAPP', 'SYS')
> AND (s.sid = r.session_id(+))
> AND s.paddr=b.addr
> GROUP BY s.username, s.serial#, r.session_id, r.error_number,
> r.status, s.sid, s.status, s.osuser,
> b.spid, s.machine,
> s.program,s.logon_time,s.resource_consumer_group;
> BEGIN
> --SET TRANSACTION READ ONLY;
> for rec_ in get_kill loop
> IF rec_.sid>0 then
> dbms_output.put_line('SID: '||rec_.sid||' ---
> '||rec_.username||' serial#--'||rec_.serial);
> ALTER SYSTEM KILL SESSION 'rec_.sid, rec_.serial';
> dbms_output.put_line('Session '||rec_.sid||' Killed');
> END IF;
> END LOOP;
> END;
> ---------------------------
> mohon bantuan kawan-kawan tentang masalah diatas. terima kasih telah
> membaca dan menanggapinya.
>
>
> siswanto
Send instant messages to your online friends http://asia.messenger.yahoo.com
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.blogspot.com
Mirror: http://indooracle.wordpress.com
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.blogspot.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
