GV$JAVA_LIBRARY_CACHE_MEMORY

select
inst_id,                 -- INST_ID NUMBER
decode                   -- LC_NAMESPACE VARCHAR2(15)
  (kgljsim_namespace, 0, 'SQL AREA', 1, 'TABLE/PROCEDURE', 2, 'BODY', 3, 'TRIGGER', 4, 'INDEX', 5, 'CLUSTER', 6, 'OBJECT', 7, 'PIPE', 13, 'JAVA SOURCE', 14, 'JAVA RESOURCE', 32, 'JAVA DATA', '?'),
kgljsim_pincnt,          -- LC_INUSE_MEMORY_OBJECTS NUMBER
kgljsim_pinmem,          -- LC_INUSE_MEMORY_SIZE NUMBER
kgljsim_unpincnt,        -- LC_FREEABLE_MEMORY_OBJECTS NUMBER
kgljsim_unpinmem         -- LC_FREEABLE_MEMORY_SIZE NUMBER
from
x$kgljmem 
where
kgljsim_namespace<8 or kgljsim_namespace=13 or kgljsim_namespace=14 or kgljsim_namespace=32 
union select
inst_id,                 -- INST_ID NUMBER
'OTHER/SYSTEM',          -- LC_NAMESPACE VARCHAR2(15)
sum                      -- LC_INUSE_MEMORY_OBJECTS NUMBER
  (kgljsim_pincnt) sum_pincnt,
sum                      -- LC_INUSE_MEMORY_SIZE NUMBER
  (kgljsim_pinmem) sum_pinmem,
sum                      -- LC_FREEABLE_MEMORY_OBJECTS NUMBER
  (kgljsim_unpincnt) sum_unpincnt,
sum                      -- LC_FREEABLE_MEMORY_SIZE NUMBER
  (kgljsim_unpinmem) sum_unpinmem 
from
x$kgljmem 
where
not (kgljsim_namespace<8 or kgljsim_namespace=13 or kgljsim_namespace=14 or kgljsim_namespace=32) 
group by inst_id
home |  up  |  mailbox