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
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
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
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.
3. Create a Cache Administration User in the Oracle Database
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
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.
5. Grant Data Access Privileges to the Cache Administration User
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.
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