Manually Creating Oracle Database Objects for Cache Groups with Autorefresh

There are certain procedures you need to do if you created the Oracle Database objects used to enforce the predefined behaviors of a cache group with autorefresh with the initCacheAdminSchema.sql script.

See The initCacheAdminSchema.sql Script.

Set the autorefresh state to OFF when creating the cache group.
Run the ttIsql utility's cachesqlget command with the INCREMENTAL_AUTOREFRESH option and the INSTALL flag as the TimesTen cache administration user. This command generates a SQL*Plus script used to create a cache log table and a trigger in the Oracle database for each Oracle Database table that is cached in the autorefresh cache group. These Oracle Database objects track updates on the cached Oracle Database tables so that the updates can be automatically refreshed to the cache tables.

Note:

The ttCacheSQLGet built-in procedure provides the same functionality as the ttIsql cachesqlget command.
Use SQL*Plus to run the script generated by the ttIsql utility's cachesqlget command as the sys user.
Run an ALTER CACHE GROUP statement to change the autorefresh state of the cache group to PAUSED.

The following examples shows how to create a read-only cache group when Oracle Database objects are created with the initCacheAdminSchema.sql script.

The first statement creates a read-only cache group customer_orders with the autorefresh state set to OFF. The SQL*Plus script generated by the ttIsql utility's cachesqlget command is saved to the /tmp/obj.sql file. The last statement changes the autorefresh state of the cache group to PAUSED.

CREATE READONLY CACHE GROUP customer_orders
AUTOREFRESH STATE OFF
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  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.customer(cust_num));

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachesqlget INCREMENTAL_AUTOREFRESH customer_orders INSTALL /tmp/obj.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/obj
SQL> exit

ALTER CACHE GROUP customer_orders SET AUTOREFRESH STATE PAUSED;

See ttIsql and ttCacheSqlGet in Oracle TimesTen In-Memory Database Reference.