Oracle PL/SQL Tutorial/System Tables Data Dictionary/DB OBJECT CACHE

Query v$db_object_cache

SQL>
SQL> SELECT owner, name, type, sharable_mem, kept
  2  FROM   v$db_object_cache
  3  WHERE  type like "P%"
  4  and    rownum < 50
  5  ORDER BY owner, name;
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
CTXSYS
DRIIXS
PACKAGE BODY                        25153 NO
CTXSYS
DRIREC
PACKAGE                              5517 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
CTXSYS
DRISPL
PACKAGE                              5517 NO
CTXSYS
DRISPL
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE BODY                        25153 NO
CTXSYS
DRIUTL
PACKAGE                              5517 NO
CTXSYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
DRIXMD
PACKAGE BODY                        85629 NO
sqle
ADJUST_SALARY
PROCEDURE                           22980 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
sqle
CORE_PROCESS
PROCEDURE                            2499 NO
sqle
DISPLAY_CUSTOMERS
PROCEDURE                            1432 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
sqle
DO_QUERY
PROCEDURE                            1423 NO
sqle
ERRORS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE                              9613 NO
sqle
ERROR_TEST1
PROCEDURE                           14786 NO
sqle
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
HOCKEY_PASS
PROCEDURE                            2498 NO
sqle
PROCESPRODUCTS
PROCEDURE                            1429 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
sqle
SHOWVALS
PROCEDURE                            2495 NO
sqle
SHOW_ORDERS
PROCEDURE                           21906 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
sqle
TEST_EMPLOYEE_CURSORS
PROCEDURE                           21916 NO
SYS
AW_DROP_PROC
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PROCEDURE                           13712 NO
SYS
DBMS_ADVISOR
PACKAGE                              1424 NO
SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
DBMS_ALERT
PACKAGE BODY                        18914 NO
SYS
DBMS_APPLICATION_INFO
PACKAGE                             17817 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_DEFER_IMPORT_INTERNAL
PACKAGE                             13726 NO
SYS
DBMS_HA_ALERTS_PRVT
PACKAGE                              5527 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_IREFRESH
PACKAGE BODY                        25181 NO
SYS
DBMS_IREFRESH
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE                             30097 NO
SYS
DBMS_LOB
PACKAGE BODY                        17832 NO
SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
DBMS_LOB
PACKAGE                             54668 NO
SYS
DBMS_OUTPUT
PACKAGE BODY                         9615 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_PIPE
PACKAGE                             21901 NO
SYS
DBMS_PRVT_TRACE
PACKAGE BODY                        13715 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_REGISTRY_SYS
PACKAGE BODY                        52641 NO
SYS
DBMS_REGISTRY_SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE                             17813 NO
SYS
DBMS_REPCAT_COMMON_UTL
PACKAGE BODY                        23046 NO
SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
DBMS_REPCAT_DECL
PACKAGE BODY                         6596 NO
SYS
DBMS_REPCAT_INTERNAL
PACKAGE BODY                        14792 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_REPCAT_RGT_UTL
PACKAGE BODY                        16935 NO
SYS
DBMS_REPCAT_UTL
PACKAGE BODY                       231003 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_REPUTIL
PACKAGE                              5520 NO
SYS
DBMS_SPACE
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE                             54670 NO
SYS
DBMS_SQL
PACKAGE                            153016 NO
SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
DBMS_SQLTUNE
PACKAGE                             18880 NO
SYS
DBMS_STREAMS_ADM_UTL
PACKAGE                             67000 NO

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_STREAMS_ADM_UTL
PACKAGE BODY                        83392 NO
SYS
DBMS_SYSTEM
PACKAGE                              5519 NO
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
SYS
DBMS_SYS_SQL
PACKAGE BODY                        67200 NO
SYS
DBMS_TRANSACTION
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
PACKAGE                             17812 NO
SYS
DBMS_UTILITY
PACKAGE BODY                        41652 NO
SYS
OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE                         SHARABLE_MEM KEP
---------------------------- ------------ ---
HTP
PACKAGE                             71047 NO
SYS
XML_SCHEMA_NAME_PRESENT
PACKAGE                             13723 NO

49 rows selected.
SQL>

Union V$DB_OBJECT_CACHE and V$SQLAREA

SQL>
SQL>
SQL> SELECT TO_CHAR(SHARABLE_MEM / 1000 ,"999999") SZ,
  2         DECODE(KEPT_VERSIONS,0,"      ",
  3         RPAD("YES(" || TO_CHAR(KEPT_VERSIONS)  || ")" ,6)) KEEPED,
  4         RAWTOHEX(ADDRESS) || ","  || TO_CHAR(HASH_VALUE)  NAME,
  5         SUBSTR(SQL_TEXT,1,354) EXTRA, 1 ISCURSOR
  6  FROM   V$SQLAREA
  7  WHERE  SHARABLE_MEM > 1000
  8  and    rownum < 2
  9  UNION
 10  SELECT TO_CHAR(SHARABLE_MEM / 1000 ,"999999") SZ,
 11         DECODE(KEPT,"YES","YES   ","      ") KEEPED,
 12         OWNER || "."  || NAME  ||
 13         LPAD(" ",29 - (LENGTH(OWNER) + LENGTH(NAME) ) )  ||
 14         "("  || TYPE  || ")"  NAME, NULL  EXTRA, 0 ISCURSOR
 15  FROM   V$DB_OBJECT_CACHE V
 16  WHERE  SHARABLE_MEM > 1000
 17  and    rownum < 2
 18  ORDER BY 1 DESC;
SZ      KEEPED
------- ------
NAME
--------------------------------------------------------------------------------
EXTRA
--------------------------------------------------------------------------------
  ISCURSOR
----------
     17
20A7DF44,921436339
select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, stat
us_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time, m
rct_snap_time_num, mrct_purge_time_num, snapint_num, retention_num, swrf_version
SZ      KEEPED
------- ------
NAME
--------------------------------------------------------------------------------
EXTRA
--------------------------------------------------------------------------------
  ISCURSOR
----------
, registration_status, mrct_baseline_id, topnsql from wrm$_wr_control where dbid
 = :dbid
         1
      2
SZ      KEEPED
------- ------
NAME
--------------------------------------------------------------------------------
EXTRA
--------------------------------------------------------------------------------
  ISCURSOR
----------
.select id, name, block_size, advice_status,                  size_for_estimate,
 size_factor, buffers_for_estimate,        estd_physical_read_factor, estd_physi
cal_reads,              estd_physical_read_time,
     estd_pct_of_db_time_for_reads,                               estd_cluster_r
eads,                                          estd_cluster_read_time
SZ      KEEPED
------- ------
NAME
--------------------------------------------------------------------------------
EXTRA
--------------------------------------------------------------------------------
  ISCURSOR
----------
                            from   gv$db_cache_advice where inst_id = userenv("i
nstance")(CURSOR)
         0

SQL>