Task 3: Create a Dynamic Read-Only Cache Group on the TimesTen Database

Create a dynamic read-only cache group with autorefresh on the TimesTen database.

1. Continuing as the TimesTen cache administration user, create a dynamic read-only cache group with autorefresh with the CREATE DYNAMIC READONLY CACHE GROUP SQL statement. Use the unique index columns as the primary key definition. Note that autorefresh is configured by default for read-only cache groups.

   Command> CREATE DYNAMIC READONLY CACHE GROUP customer_orders
   AUTOREFRESH
   FROM sales.customers
    (cust_num NUMBER(6) NOT NULL,
     region   VARCHAR2(10),
     name     VARCHAR2(50),
     PRIMARY KEY(cust_num)),
   sales.orders
    (ord_num      NUMBER(10) NOT NULL,
     cust_num     NUMBER(6) NOT NULL,
     when_placed  DATE NOT NULL,
     when_shipped DATE NOT NULL,
     PRIMARY KEY(ord_num),
     FOREIGN KEY(cust_num) REFERENCES sales.customers(cust_num));
   Command> exit;
   Disconnecting...
   Done.

   Note:

   This SQL statement creates the cache group and the cache tables on the TimesTen database.

   When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle Database tables and choose an equivalent or compatible data type for the columns in the cache tables. See Mappings Between Oracle Database and TimesTen Data Types

2. Start the ttIsql utility and connect to the cache1 DSN as the instance administrator.

   Grant the SELECT privilege on the sales.customers and sales.orders cache tables to the TimesTen cache administration user so that this user can issue a SELECT query on this table.

   % ttIsql cache1
   Command> GRANT SELECT ON sales.customers TO cacheadmin;
   Command> GRANT SELECT ON sales.orders TO cacheadmin;
   Command> exit;
   Disconnecting...
   Done.

Since this is a dynamic read-only cache group with autorefresh, all changes on the Oracle database are automatically refreshed into the cache group at the default interval. You can dynamically request data that does not exist in the cache with a qualified SQL statement. You should not run any DML statements directly against any read-only cache tables in the TimesTen database.