Event and Session script

Hi friends, being a DBA is a challenging job when at times you are facing multiple issues.
It becomes difficult to keep track of everything, hence the use of scripts to automate trivial things is very essential.
Here I will be sharing some of my scripts for trivial events which may prove useful for many.

In this blog I am sharing the script for checking the session details given the sid and instance number of the session and for checking which event is going on in the database.

The below script can be used for checking the events going on in the database. Just copy and paste in a file and save it as <any_name>.sql

set lines 170
set pages 1000
col event format a35
select inst_id,event,count(*) from gv$session_wait
group by inst_id,event order by 3
/
col inst_id for 999
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
col MINS for 99999
select distinct
w.inst_id,w.sid,s.username,substr(w.event,1,25) event,s.type,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,--q.CHILD_NUMBER CHILD,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS"
from gv$session_wait w,gv$session s,gv$sql q
where
w.event like '%&event%' and
w.sid=s.sid and
w.inst_id=s.inst_id and
w.inst_id=q.inst_id and
s.SQL_HASH_VALUE=q.HASH_VALUE and
s.status='ACTIVE' and
s.username is not null
order by "MINS"
/

This is how the output of event script looks

The below script can be used for fetching the details of the session based on instance id and SID.

set serveroutput on
set echo off feed off veri off
exec dbms_output.enable(20000)
accept IID prompt 'Enter Instance No: '
accept SID prompt 'Enter Oracle SID: '
DECLARE
v_sid number;
v_inst number;
s gv$session%ROWTYPE;
p gv$process%ROWTYPE;
BEGIN
begin
select sid ,inst_id into v_sid , v_inst
from gv$session s
where sid = &&SID
and inst_id = &&IID;
exception
when no_data_found then
dbms_output.put_line('Unable to find SID &&SID!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;
select * into s from gv$session where sid = v_sid and inst_id = v_inst;
select * into p from gv$process where addr = s.paddr and inst_id = v_inst;
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Instance no : '|| s.inst_id);
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('CLIENT_INFO : '|| 'CLIENT_INFO: '||s.CLIENT_INFO);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Sql ID : '|| s.sql_id);
dbms_output.put_line('prev Sql ID : '|| s.prev_sql_id);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from gv$sqltext
where HASH_VALUE = s.sql_hash_value and INST_ID=s.inst_id order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from gv$sqltext
where HASH_VALUE = s.prev_hash_value and INST_ID=s.inst_id order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from gv$session_wait where sid = s.sid and INST_ID=s.inst_id) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from gv$session_connect_info where sid = s.sid and INST_ID=s.inst_id) loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.object_name,
'TD', o.object_name,
'TM', o.object_name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from gv$lock l, dba_objects o
where sid = s.sid
and l.INST_ID=s.inst_id
and l.id1 = o.object_id ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/
undef SID
set feedback on
set feed on

This how the output of session script looks