Querying cache segment sizes and assignments

Administrator Guide : Performance tuning : Cache segment partitioning : Querying cache segment sizes and assignments

The cache segments and table assignments are stored in the system tables SYS_CACHESEGMENTS and SYS_CACHESEGMENT_CONDITIONS. Use the following type of statements to query the cache segment information.

To list tables that use a given cache segment:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_TABLES, SYS_CACHESEGMENT_CONDITIONS
where ID = BASE_TABLE_ID
and CACHESEGMENT_NAME = 'segment_name'

For example:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_TABLES, SYS_CACHESEGMENT_CONDITIONS
where ID = BASE_TABLE_ID
and CACHESEGMENT_NAME = 'CS1'

TABLE_NAME CACHESEGMENT_NAME
---------- -----------------
TAB_TIMESTAMP CS1
TAB_INTEGER CS1
2 rows fetched.

To list cache segments that are used by a given table:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_CACHESEGMENT_CONDITIONS, SYS_TABLES
WHERE SYS_TABLES.ID = SYS_CACHESEGMENT_CONDITIONS.BASE_TABLE_ID
and SYS_TABLES.TABLE_NAME = 'table_name'

For example:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_CACHESEGMENT_CONDITIONS, SYS_TABLES
WHERE SYS_TABLES.ID = SYS_CACHESEGMENT_CONDITIONS.BASE_TABLE_ID
and SYS_TABLES.TABLE_NAME = 'TAB_TIMESTAMP'

TABLE_NAME CACHESEGMENT_NAME
---------- -----------------
TAB_TIMESTAMP CS1
1 rows fetched.