V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_OBJECTS displays all the objects (both cached results and dependencies) and their attributes.

Column Datatype Description
ID NUMBER Identifier for the cache object (also the ID of the first block)
TYPE VARCHAR2(10) Type of the cache object:

  • Result

  • Dependency
STATUS VARCHAR2(9) Status of the object:

  • New - Result is still under construction

  • Published - Result is available for use

  • Bypass - Result will be bypassed from use

  • Expired - Result has exceeded expiration time

  • Invalid - Result is no longer available for use
BUCKET_NO NUMBER Internal hash bucket for the object
HASH NUMBER Hash value for the object
NAME VARCHAR2(128) Name (for example, SQL prefix or PL/SQL function name)
NAMESPACE VARCHAR2(5) Namespace:

  • SQL

  • PLSQL
CREATION_TIMESTAMP DATE Time when the object was created
CREATOR_UID NUMBER UID that created the object
DEPEND_COUNT NUMBER Number of dependencies (TYPE = Result) or dependents (TYPE = Dependency)
BLOCK_COUNT NUMBER Total number of blocks in the cached object
SCN NUMBER Build SCN (TYPE = Result) or invalidation SCN (TYPE = Dependency)
COLUMN_COUNT NUMBER Number of columns in the cached resultFoot 1 
PIN_COUNT NUMBER Number of active scans on this resultFootref 1
SCAN_COUNT NUMBER Total number of scans initiated on the cached resultFootref 1
ROW_COUNT NUMBER Total number of rows in the cached resultFootref 1
ROW_SIZE_MAX NUMBER Size of the largest row (in bytes)Footref 1
ROW_SIZE_MIN NUMBER Size of the smallest row (in bytes)Footref 1
ROW_SIZE_AVG NUMBER Average size of a row (in bytes)Footref 1
BUILD_TIME NUMBER Amount of time (in hundredths of a second) it took to build the cached resultFootref 1
LRU_NUMBER NUMBER LRU list position (the smaller the value, the more recent the usage)Footref 1
OBJECT_NO NUMBER Dictionary object number of the dependency objectFoot 2 
INVALIDATIONS NUMBER Number of times the object has invalidated its dependentsFootref 2
SPACE_OVERHEAD NUMBER Overhead (in bytes) for the resultFootref 1
SPACE_UNUSED NUMBER Unused space (in bytes) for the resultFootref 1
CACHE_ID VARCHAR2(93) CacheId for the result (object name if it's a dependency)
CACHE_KEY VARCHAR2(93) CacheKey for the result (object name if it's a dependency)
DB_LINKFoot 3  VARCHAR2(3) Possible values:

  • YES: If the result cache object references a remote database object

  • NO: If the result cache object does not reference a remote database object
CHECKSUMFootref 3 NUMBER Checksum for the result object. The checksum is computed over all the blocks in the result cache object minus the object header.

Footnote 1 These columns are only valid for TYPE = Result; otherwise, they are NULL.

Footnote 2 These columns are only valid for TYPE = Dependency; otherwise, they are NULL.

Footnote 3 This column is available starting with Oracle Database 11g Release 2 (11.2.0.4)