37 lines
1.2 KiB
Plaintext
Executable File
37 lines
1.2 KiB
Plaintext
Executable File
-- note down SADDR and P1RAW from the following query
|
|
select s.sid, s.saddr, sw.p1raw
|
|
from gv$session_wait sw, gv$session s
|
|
where sw.sid = s.sid and sw.event='library cache pin';
|
|
|
|
-- use previous SADDR and P1RAW and note down KGLLKUSE
|
|
select b.KGLLKUSE from dba_kgllock w , dba_kgllock b
|
|
where w.KGLLKHDL = b.KGLLKHDL
|
|
and w.KGLLKREQ > 0 and b.KGLLKMOD > 0
|
|
and w.KGLLKTYPE = b.KGLLKTYPE
|
|
and w.KGLLKUSE = '000000025C47A5F0' -- SADDR
|
|
and w.KGLLKHDL = '000000008AEE0AB8' -- P1RAW
|
|
;
|
|
|
|
-- use previous KGLLKUSE to find the SID
|
|
select sid from gv$session s
|
|
where saddr in ('000000025DEC3278');
|
|
|
|
-- use previous SID to find session detail
|
|
select
|
|
i.instance_name instance_name
|
|
, s.sid sid
|
|
, s.serial# serial_id
|
|
, s.status session_status
|
|
, s.username oracle_username
|
|
, s.osuser os_username
|
|
, p.spid os_pid
|
|
, s.terminal session_terminal
|
|
, s.machine session_machine
|
|
, s.program session_program
|
|
from
|
|
gv$session s
|
|
inner join gv$process p on (s.paddr = p.addr and s.inst_id = p.inst_id)
|
|
inner join gv$instance i on (p.inst_id = i.inst_id)
|
|
where
|
|
s.sid in (516);
|