Friday, 5 August 2011

Oracle Unix Pid to oracle sql

Oracle Unix Pid to oracle sql..

http://anuj-singh.blogspot.com/2017/08/oracle-process-info.html

Unix pid to sql
Unix pid to oraclesql
pid to sql

------

set serveroutput on size 50000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '


DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
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('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('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, '990.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 sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value 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 sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value 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 sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) 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.name,
'TD', o.name,
'TM', o.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 sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) 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 1

/*

FINA: ORA7> @\tmp\u
Enter Unix process id: 13263
=====================================================================
SID/Serial : 67,558
Foreground : PID: 13262 - runform30@bel2 (TNS interface)
Shadow : PID: 13263 - oracle@bel2 (TNS V1-V2)
Terminal : ttyq5/ ttyq5
OS User : e8677 on bel2
Ora User : ADMN
Status Flags: INACTIVE DEDICATED USER
Tran Active : NONE
Login Time : Mon 14:15:14
Last Call : Mon 14:38:05 - 1.2 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
SELECT TO_CHAR(DECODE(LENGTH(:b1),8,TO_DATE(:b2,'DDMMRRRR'),9,TO
_DATE(:b3,'DDMONRRRR'),10,TO_DATE(:b4,'DD/MM/RRRR'),11,TO_DATE(:
b5,'DD-MON-RRRR')),'DD-MON-RRRR') FROM DUAL
Previous SQL statement:
Session Waits:
WAITING: SQL*Net message from client
Locks:
=========================

Oracle 10g +

set serveroutput on size 50000 echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '

DECLARE
v_sid number;
s v$session%ROWTYPE;
p v$process%ROWTYPE;
BEGIN
begin
select sid into v_sid from v$process p, v$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from v$session where sid = v_sid;
select * into p from v$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
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('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('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, '990.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('Session status  : '|| s.status);
dbms_output.put_line('Session event   : '|| s.event);
dbms_output.put_line('Blocking Session: '|| s.blocking_session);
dbms_output.put_line('Sql Id       : '|| s.sql_id);
dbms_output.put_line('Prev Sql Id  : '|| s.PREV_SQL_ID);
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from v$sqltext
where HASH_VALUE = s.sql_hash_value 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 v$sqltext where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from v$session_connect_info where sid = s.sid) 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.name,
'TD', o.name,
'TM', o.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 v$lock l, obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) 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 1

============

SID to SQL


 set serveroutput on
define inst_id=1
define sid=2650

DECLARE
 v_sid number;
 vs_cnt number;
 s sys.gv_$session%ROWTYPE;
 p sys.gv_$process%ROWTYPE;
 cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
 dbms_output.put_line('=====================================================================');
 select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
 open cur_c1;
 LOOP
 FETCH cur_c1 INTO v_sid;
 EXIT WHEN (cur_c1%NOTFOUND);
 select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
 select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
 dbms_output.put_line('INST_ID : '|| 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('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('Details : '|| s.action||' - '||s.module);
 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, '99999990.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 sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&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 sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&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 sys.gv_$session_wait where sid = s.sid and inst_id='&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 sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
 dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
 end loop;
 dbms_output.put_line('Locks:');
 for c1 in ( select /*+ RULE */ 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.name,
 'TD', o.name,
 'TM', o.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 sys.gv_$lock l, sys.obj$ o
 where sid = s.sid
 and inst_id='&inst_id'
 and l.id1 = o.obj#(+) )
 loop
 dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
 end loop;
 dbms_output.put_line('=====================================================================');
 END LOOP;

close cur_c1;
exception
 when no_data_found then
 dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
 dbms_output.put_line('=====================================================================');
 return;
 when others then
 dbms_output.put_line(sqlerrm);
 return;
END;
/

with sql id


define 1=58982


set serveroutput on size 50000
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select distinct sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1')
and rownum<2;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Sql_id/Previous: '|| 'Sql_id: '||s.SQL_ID||' - '||s.PREV_SQL_ID);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
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('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, '990.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 sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value 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 sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value 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 sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) 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.name,
'TD', o.name,
'TM', o.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 sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) 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 1

====


set linesize 500 pagesize 300
col SESSION  for a50
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE",ssn.sql_id,prev_sql_id
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr
AND ssn.paddr = prc.addr (+)
order by 3 desc;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Friday, 5 August 2011

Oracle Unix Pid to oracle sql

Oracle Unix Pid to oracle sql..

No comments:

Oracle DBA

anuj blog Archive