Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Union V$DB_OBJECT_CACHE and V$SQLAREA

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>