Union V$DB_OBJECT_CACHE and V$SQLAREA : DB_OBJECT_CACHE « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. DB_OBJECT_CACHE

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>