Which objects currently cached in shared pool?

Sometimes, I met with question like, "Which objects currently held in Oracle's shared pool?".
Solution: Oracle supplies dynamic performance view called V$DB_OBJECT_CACHE



SQL> desc v$db_object_cache
Name Null? Type
----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(64)
NAME VARCHAR2(1000)
DB_LINK VARCHAR2(64)
NAMESPACE VARCHAR2(28)
TYPE VARCHAR2(28)
SHARABLE_MEM NUMBER
LOADS NUMBER
EXECUTIONS NUMBER
LOCKS NUMBER
PINS NUMBER
KEPT VARCHAR2(3)
CHILD_LATCH NUMBER
INVALIDATIONS NUMBER


Here,

OWNER - is the owner of the object
NAME - is object name
SHARABLE_MEM - Amount of memory in the shared pool consumed by the object


Lets check what type of objects currently in shared pool:

SQL> select distinct type from v$db_object_cache;

TYPE
----------------------------
INVALID TYPE
RULESET
QUEUE
SEQUENCE
PACKAGE
LIBRARY
PACKAGE BODY
RSRC PLAN
PUB_SUB
NON-EXISTENT
NOT LOADED

TYPE
----------------------------
TABLE
INDEX
VIEW
RSRC CONSUMER GROUP
SYNONYM
CURSOR
TYPE
CLUSTER

19 rows selected.

Lets check which package and procedures currently in shared pool and how much memory they occupied:

SQL> select name, SHARABLE_MEM from v$db_object_cache where type in ('PROCEDURE', 'PACKAGE');

NAME SHARABLE_MEM
------------------------------ ------------
DBMS_BACKUP_RESTORE 259567
DBMS_AQADM_SYSCALLS 46487
DBMS_HA_ALERTS_PRVT 17815
PRVT_ADVISOR 75152
DBMS_PRVT_TRACE 5523
DBMS_STANDARD 26001
PRVT_HDM 17804
PLITBLM 5515
DBMS_PRVTAQIP 43457
STANDARD 439692
DBMS_SCHEDULER 88566

11 rows selected.