-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_object_cache.sql |
-- | CLASS : Database Administration |
-- | PURPOSE : Summary of objects in the shared pool cache. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET LINESIZE 155
SET PAGESIZE 9999
COLUMN inst_id HEAD "Inst.|ID"
COLUMN owner FORMAT a10 HEAD "Owner"
COLUMN name FORMAT a30 HEAD "Name"
COLUMN db_link FORMAT a7 HEAD "DB Link"
COLUMN namespace FORMAT a25 HEAD "Namespace"
COLUMN type FORMAT a18 HEAD "Type"
COLUMN sharable_mem FORMAT 99,999,999,999 HEAD "Sharable|Memory"
COLUMN loads HEAD "Loads"
COLUMN executions FORMAT 99,999,999 HEAD "Executions"
COLUMN locks HEAD "Locks"
COLUMN pins HEAD "Pins"
COLUMN kept FORMAT a5 HEAD "Kept?"
COLUMN child_latch HEAD "Child Latch"
COLUMN hash_value HEAD "Hash Value"
COLUMN address HEAD "Address"
COLUMN paddress HEAD "Paddress"
COLUMN crsr_plan_hash_value HEAD "Cursor Plan|Hash Value"
COLUMN kglobt02 HEAD "kglobt02"
BREAK ON report
COMPUTE sum OF sharable_mem ON report
SELECT
inst_id inst_id
, kglnaown owner
, kglnaobj name
-- , kglnadlk db_link
, DECODE( kglhdnsp
, 0 , 'CURSOR'
, 1 , 'TABLE/PROCEDURE'
, 2 , 'BODY'
, 3 , 'TRIGGER'
, 4 , 'INDEX'
, 5 , 'CLUSTER'
, 6 , 'OBJECT'
, 13, 'JAVA SOURCE'
, 14, 'JAVA RESOURCE'
, 15, 'REPLICATED TABLE OBJECT'
, 16, 'REPLICATION INTERNAL PACKAGE'
, 17, 'CONTEXT POLICY'
, 18, 'PUB_SUB'
, 19, 'SUMMARY'
, 20, 'DIMENSION'
, 21, 'APP CONTEXT'
, 22, 'STORED OUTLINE'
, 23, 'RULESET'
, 24, 'RSRC PLAN'
, 25, 'RSRC CONSUMER GROUP'
, 26, 'PENDING RSRC PLAN'
, 27, 'PENDING RSRC CONSUMER GROUP'
, 28, 'SUBSCRIPTION'
, 29, 'LOCATION'
, 30, 'REMOTE OBJECT'
, 31, 'SNAPSHOT METADATA'
, 32, 'JAVA SHARED DATA'
, 33, 'SECURITY PROFILE'
, 'INVALID NAMESPACE'
) namespace
, DECODE ( BITAND(kglobflg, 3)
, 0, 'NOT LOADED'
, 2, 'NON-EXISTENT'
, 3, 'INVALID STATUS'
, DECODE ( kglobtyp
, 0 , 'CURSOR'
, 1 , 'INDEX'
, 2 , 'TABLE'
, 3 , 'CLUSTER'
, 4 , 'VIEW'
, 5 , 'SYNONYM'
, 6 , 'SEQUENCE'
, 7 , 'PROCEDURE'
, 8 , 'FUNCTION'
, 9 , 'PACKAGE'
, 10, 'NON-EXISTENT'
, 11, 'PACKAGE BODY'
, 12, 'TRIGGER'
, 13, 'TYPE'
, 14, 'TYPE BODY'
, 15, 'OBJECT'
, 16, 'USER'
, 17, 'DBLINK'
, 18, 'PIPE'
, 19, 'TABLE PARTITION'
, 20, 'INDEX PARTITION'
, 21, 'LOB'
, 22, 'LIBRARY'
, 23, 'DIRECTORY'
, 24, 'QUEUE'
, 25, 'INDEX-ORGANIZED TABLE'
, 26, 'REPLICATION OBJECT GROUP'
, 27, 'REPLICATION PROPAGATOR'
, 28, 'JAVA SOURCE'
, 29, 'JAVA CLASS'
, 30, 'JAVA RESOURCE'
, 31, 'JAVA JAR'
, 32, 'INDEX TYPE'
, 33, 'OPERATOR'
, 34, 'TABLE SUBPARTITION'
, 35, 'INDEX SUBPARTITION'
, 36, 'REPLICATED TABLE OBJECT'
, 37, 'REPLICATION INTERNAL PACKAGE'
, 38, 'CONTEXT POLICY'
, 39, 'PUB_SUB'
, 40, 'LOB PARTITION'
, 41, 'LOB SUBPARTITION'
, 42, 'SUMMARY'
, 43, 'DIMENSION'
, 44, 'APP CONTEXT'
, 45, 'STORED OUTLINE'
, 46, 'RULESET'
, 47, 'RSRC PLAN'
, 48, 'RSRC CONSUMER GROUP'
, 49, 'PENDING RSRC PLAN'
, 50, 'PENDING RSRC CONSUMER GROUP'
, 51, 'SUBSCRIPTION'
, 52, 'LOCATION'
, 53, 'REMOTE OBJECT'
, 54, 'SNAPSHOT METADATA'
, 55, 'IFS'
, 56, 'JAVA SHARED DATA'
, 57, 'SECURITY PROFILE'
, 'INVALID TYPE'
)
) type
, kglobhs0 +
kglobhs1 +
kglobhs2 +
kglobhs3 +
kglobhs4 +
kglobhs5 +
kglobhs6 sharable_mem
, kglhdldc loads
, kglhdexc executions
, kglhdlkc locks
, kglobpc0 pins
, DECODE( kglhdkmk
, 0 ,'NO'
, 'YES'
) kept
-- , kglhdclt child_latch
-- , kglnahsh hash_value
-- , kglhdadr address
-- , kglhdpar paddress
-- , kglobt30 crsr_plan_hash_value
-- , kglobt02 kglobt02
FROM x$kglob
/
No comments:
Post a Comment