56 lines
2.6 KiB
SQL
56 lines
2.6 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
--
|
|
-- kglpn.sql by Tanel Poder (http://blog.tanelpoder.com)
|
|
--
|
|
|
|
col kglpn_object_name head OBJECT_NAME for a40
|
|
col kglpn_pinned_blocks head PINNED_BLOCKS for a13 word_wrap
|
|
col kglpn_username HEAD USERNAME for a25 wrap
|
|
|
|
-- there's a reason for the use_hash hint, we don't want to hammer libcache with a lot of single object lookups
|
|
-- which a nested loops join would cause if lots of objects are returned from x$kglpn (for a handful of rows
|
|
-- a nested loop would be better, but hash join is safer with large amount of rows in this case)
|
|
|
|
select * from (
|
|
select /*+ merge leading(p,g) use_hash(g) */
|
|
kglhdadr
|
|
-- , kglpnuse
|
|
-- , kglpnses
|
|
, s.sid
|
|
, s.username kglpn_username
|
|
, nvl2(kglnaown, kglnaown||'.', null)||kglnaobj kglpn_object_name
|
|
, SUBSTR(CASE kglpnmod WHEN 0 THEN 'None' WHEN 2 THEN 'Share' WHEN 3 THEN 'Excl' ELSE TO_CHAR(kglpnmod) END, 1,5) pin_mode
|
|
, SUBSTR(CASE kglpnreq WHEN 0 THEN 'None' WHEN 2 THEN 'Share' WHEN 3 THEN 'Excl' ELSE TO_CHAR(kglpnreq) END, 1,5) req_mode
|
|
, CASE WHEN BITAND(kglpndmk, 1)= 1 THEN '0 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 2)= 2 THEN '1 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 4)= 4 THEN '2 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 8)= 8 THEN '3 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 16)= 16 THEN '4 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 32)= 32 THEN '5 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 64)= 64 THEN '6 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 128)= 128 THEN '7 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 256)= 256 THEN '8 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 512)= 512 THEN '9 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 1024)= 1024 THEN '10 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 2048)= 2048 THEN '11 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 4096)= 4096 THEN '12 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 8192)= 8192 THEN '13 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 16384)=16384 THEN '14 ' END
|
|
|| CASE WHEN BITAND(kglpndmk, 32768)=32768 THEN '15' END kglpn_pinned_blocks
|
|
--, g.kglnahsh kglnahsh
|
|
, g.kglnahsh hash_value
|
|
, kglpncnt refcnt
|
|
from
|
|
x$kglpn p,
|
|
x$kglob g,
|
|
v$session s
|
|
where
|
|
p.kglpnhdl = g.kglhdadr
|
|
and p.kglpnuse = s.saddr (+)
|
|
)
|
|
where &1
|
|
/
|
|
|