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
--
-----------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/
