Example 1: Creating a row cache
This example creates a database, creates a row cache, and assigns
the row cache to a storage area.
SQL> CREATE DATABASE FILENAME test_db
cont> ROW CACHE IS ENABLED
cont> CREATE CACHE test1
cont> CACHE SIZE IS 100 ROWS
cont> CREATE STORAGE AREA area1
cont> CACHE USING test1;
SQL> SHOW CACHE
Cache Objects in database with filename test_db
TEST1
SQL> SHOW CACHE test1
TEST1
Cache Size: 100 rows
Row Length: 256 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Reserved Rows: 20
Sweep Rows: 3000
No Sweep Thresholds
Allocation: 100 blocks
Extent: 100 blocks
SQL> SHOW STORAGE AREA area1
AREA1
Access is: Read write
Page Format: Uniform
Page Size: 2 blocks
Area File: SQL_USER1:[DAY.V70]AREA1.RDA;1
Area Allocation: 402 pages
Area Extent Minimum: 99 pages
Area Extent Maximum: 9999 pages
Area Extent Percent: 20 percent
Snapshot File: SQL_USER1:[DAY.V70]AREA1.SNP;1
Snapshot Allocation: 100 pages
Snapshot Extent Minimum: 99 pages
Snapshot Extent Maximum: 9999 pages
Snapshot Extent Percent: 20 percent
Extent : Enabled
Locking is Row Level
Using Cache TEST1
No database objects use Storage Area AREA1
Example 2: Creating and modifying various caches
1 The cache named CUSTOMER_STATUS is created with a row length
of 577 bytes with 88000 cache "slots" for storage of live
database rows and 7000 slots for storage of snapshot copies of
rows. This cache is also configured to be memory- resident.
2 The cache named MACHINE_FLOW_IDX_1 is created with a row
length of 430 bytes with 5000 cache slots for storage of live
database rows and 12000 slots for storage of snapshot copies
of rows. This cache is set to disallow replacement of rows in
the cache.
3 The cache named SALES_CALLS is created with a row length of
160 bytes with 3000 cache "slots" for storage of live database
rows and, using the default because an explicit count was not
specified, 1000 slots for storage of snapshot copies of rows.
4 The cache named CUSTOMER_ORDER does not specify "ROW SNAPSHOT
IS ENABLED" so no snapshot row copies will be stored in this
cache.
5 The cache named "SALES" is modified to disable storage of
snapshot rows in cache.
6 The cache named "CLEARING" is modified to enable storage
of snapshot rows in the cache with a snapshot cache size of
12,345 rows.
SQL> ALTER DATABASE FILENAME HDB_DB
1 ADD CACHE CUSTOMER_STATUS
ROW LENGTH IS 577 BYTES
CACHE SIZE IS 88000 ROWS
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 7000 ROWS)
SHARED MEMORY IS PROCESS RESIDENT
2 ADD CACHE MACHINE_FLOW_IDX_1
ROW LENGTH IS 430 BYTES
CACHE SIZE IS 5000 ROWS
ROW REPLACEMENT IS DISABLED
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12000 ROWS)
3 ADD CACHE SALES_CALLS
ROW LENGTH IS 160 BYTES
CACHE SIZE IS 3000 ROWS
ROW SNAPSHOT IS ENABLED
4 ADD CACHE CUSTOMER_ORDER
ROW LENGTH IS 760 BYTES
CACHE SIZE IS 9000 ROWS
CHECKPOINT UPDATED ROWS TO DATABASE
5 ALTER CACHE SALES
ROW SNAPSHOT IS DISABLED
6 ALTER CACHE CLEARING
ROW SNAPSHOT IS ENABLED (CACHE SIZE IS 12345 ROWS);
SQL> SHOW CACHE CUSTOMER_STATUS
CUSTOMER_STATUS
Cache Size: 88000 rows
Row Length: 580 bytes
Row Replacement: Enabled
Shared Memory: Process Resident
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 7000 rows
SQL> SHOW CACHE MACHINE_FLOW_IDX_1
MACHINE_FLOW_IDX_1
Cache Size: 5000 rows
Row Length: 432 bytes
Row Replacement: Disabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 12000 rows
SQL> SHOW CACHE SALES_CALLS
SALES_CALLS
Cache Size: 3000 rows
Row Length: 160 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Snapshot in Cache: Enabled
Snapshot Cache Size: 1000 rows
SQL> SHOW CACHE CUSTOMER_ORDER
CUSTOMER_ORDER
Cache Size: 9000 rows
Row Length: 760 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Working Set Count: 10
Reserved Rows: 20
Allocation: 100 blocks
Extent: 100 blocks
Row cache: checkpoint updated rows to database