Required Configuration on the Oracle Database

Before you can cache tables from an Oracle database into In-Memory Database Cache, a cache administration user and TimesTen system tables must be created in the Oracle database. A cache administration user is an Oracle user that tracks the changes between the Oracle database and the in-memory cache database. This user must be granted sufficient privileges so that he can create triggers and have access to the Oracle data that are being cached.

1. Create a Tablespace in the Oracle Database

1. Using SQL*Plus, log into your Oracle database as the user SYS. In the example, we are using ttorcl as the TNS service name pointing to the Oracle database.

   sqlplus sys@ttorcl as sysdba




2. Create a tablespace for managing all the IMDB cache objects.

   create tablespace ttusers datafile 'ttusers.dbf' SIZE 40M;

2. Create the TimesTen Schema in the Oracle Database

1. Run the script initCacheGlobalSchema.sql located in the <install_dir>/oraclescripts directory to create the TimesTen schema in the Oracle database. The script prompts for the default tablespace for the TIMESTEN user. Enter ttusers at the prompt.

   @TimesTen/tt1122/oraclescripts/initCacheGlobalSchema.sql

   

3. Create a Cache Administration User in the Oracle Database

1. Create an Oracle account for the cache administration user. In this example, cacheadm is the cache administration user.

   create user cacheadm identified by cacheadm
   default tablespace ttusers
   quota unlimited on ttusers
   temporary tablespace temp;

4. Grant System Privileges to the Cache Administration User

1. Run the script <install_dir>/oraclescripts/grantCacheAdminPrivileges.sql to grant the necessary privileges to the cache administration user. The script prompts for the user name of the cache administration user. Enter cacheadm at the prompt.

   @TimesTen/tt1122/oraclescripts/grantCacheAdminPrivileges.sql

   

5. Grant Data Access Privileges to the Cache Administration User

1. The cache administration user is responsible for monitoring, and applying the data changes between the cache database and the Oracle database. This user needs to be granted access privileges to the Oracle tables being cached.

   In our example, which we will see in the succeeding tutorials, the hr.departments table will be cached as a read-only table, while the tables hr.employees and hr.job_history will be cached as updatable tables. Log in as the user hr and grant the following object privileges to cacheadm.

   connect hr@ttorcl

   grant select on hr.departments to cacheadm;
   grant select, insert, update, delete on hr.employees to cacheadm;
   grant select, insert, update, delete on hr.job_history to cacheadm;

For more information about configuring the Oracle database to cache data in TimesTen refer to the documentation here