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>