Oracle PL/SQL Tutorial/System Tables Data Dictionary/DB OBJECT CACHE
Query v$db_object_cache
<source lang="sql">
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></source>
Union V$DB_OBJECT_CACHE and V$SQLAREA
<source lang="sql">
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></source>
