Oracle Database

--
-- Show Object Cache Information.
--
-- The following list is an eaxmple of the object types that can be reported upon
--
-- CLUSTER
-- CURSOR
-- FUNCTION
-- INDEX
-- INVALID TYPE
-- NON-EXISTENT
-- NOT LOADED
-- PACKAGE
-- PACKAGE BODY
-- PUB_SUB
-- RSRC CONSUMER GROUP
-- SEQUENCE
-- SYNONYM
-- TABLE
-- TYPE
-- TYPE BODY
-- VIEW
-- 
-- When prompted enter an Object Type to Report on or % for all Object Types
 
SET PAUSE on
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL owner FOR A15
COL name FOR A65
COL type FOR A15
 
SELECT  owner,
        name,
        type,
        sharable_mem,
        loads,
        executions,
        locks,
        pins
FROM    V$DB_OBJECT_CACHE
WHERE TYPE LIKE UPPER('&obj_type')
ORDER   BY owner, name, type, loads
/