Monitoraggio Processi e Query- Individuare lo stato di EVENT
Vedere gli eventi della session da analizzare
SELECT username, sid, serial# ,
osuser, status
FROM v$session
WHERE trunc(logon_time) =
to_date('28/10/2017','dd/mm/yyyy')
AND machine ='nome_server'
AND service_name ='nome_sid';SELECT event FROM V$session;
SELECT event FROM v$session_wait
Di seguito la query per individuare il processo del sistema operativo (spid):
SELECT spid
FROM v$process
WHERE NOT EXISTS (SELECT 1
FROM v$session
WHERE paddr = addr);
Per identificate la sessione in corso.
SELECT s.sid,
s.serial#, p.spid
FROM v$process p,
v$session s
WHERE p.addr = s.paddr
AND s.username = 'schema';
Per individuare i parametri da killare
select 'alter system kill session '||''''|| s.sid ||','|| s.serial# ||''';'
FROM v$session s,v$process p
WHERE p.addr=s.paddr
AND s.sid =
and p.program=
SQL> alter system kill session '141,44';
# kill -9 <spid>
SELECT*
FROM v$sql_monitor M
WHERE service_name ='nome_sid'
AND username ='schema'
AND user# = 239
AND sid = 495
AND status ='EXECUTING'
ORDER BY
last_refresh_time desc;
SELECT *
FROM v$sql Q
WHERE Q.sql_id = '953mxjqjyurwn' --M.sql_id
AND Q.service = 'nome_sid';
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.service_name,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
s.last_call_et AS last_call_et_secs,
s.module,
s.action,
s.client_info,
s.client_identifier
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;