Set Up the Oracle Database to Cache Data

You need to complete tasks in the Oracle database before using TimesTen Cache. The tasks are described in the following sections:

Create the Oracle Database Users

Before you can use TimesTen Cache, you need to create the following users in your Oracle database:

A cache administration user. This user creates and maintains Oracle Database objects that store information about the cache environment. This user also enforces predefined behaviors of cache group types.
One or more schema users who owns Oracle Database tables that are cached in a TimesTen database.

See Create the Oracle Database Users and Default Tablespace in the Oracle TimesTen In-Memory Database Cache Guide.

This example creates the cacheuser_grid cache administration user and the oratt_grid schema user in the Oracle Database.

Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle Database as the sys user. Then, create a default tablespace for the schema user and a default tablespace for the cache admininstration user.
```
sqlplus sys/syspwd@oracache as sysdba
```
Create a tablespace for the schema user (oratt_grid).
```
CREATE TABLESPACE cachegridtablespace1 DATAFILE 'datattgrid1.dbf' SIZE 100M;
```
The output is the following.
```
Tablespace created.
```
Create a tablespace for the cache administration user (cacheuser_grid).
```
CREATE TABLESPACE cachegridtablespace2 DATAFILE 'datattgrid2.dbf' SIZE 100M;
```
The output is the following.
```
Tablespace created.
```
See Create the Oracle Database Users and Default Tablespace in the Oracle TimesTen In-Memory Database Cache Guide.
Use SQL*Plus to create the schema user. Grant this schema user the minimum privileges required to create tables in the Oracle Database to be cached in your TimesTen database.
This example creates the oratt_grid schema user.
```
CREATE USER oratt_grid IDENTIFIED BY oraclepwd 
DEFAULT TABLESPACE cachegridtablespace1 QUOTA UNLIMITED ON cachegridtablespace1;
```
The output is the following.
```
User created.
```
```
GRANT CREATE SESSION, RESOURCE TO oratt_grid;
```
The output is the following.
```
Grant succeeded.
```
Use SQL*Plus to create the cache administration user. Later, you will use the same name in the cacheUser metadata file.
This example creates the cacheuser_grid user.
```
CREATE USER cacheuser_grid IDENTIFIED BY oraclepwd 
DEFAULT TABLESPACE cachegridtablespace2 QUOTA UNLIMITED ON cachegridtablespace2;
```
The output is the following.
```
User created.
```
Commit and then exit SQL*Plus.
```
commit;
```
```
exit;
```
Exit the shell.
```
exit;
```

Grant Privileges to the Cache Administration User

The cache administration user needs a specific set of privileges to work with TimesTen cache groups. TimesTen provides the grantCacheAdminPrivileges.sql SQL*Plus script as part of the TimesTen installation distribution. You run this script in your Oracle database to grant the cache administration user the minimum set of privileges required to perform cache operations. See Grant Privileges to the Oracle Cache Administration User and see Required Privileges for Cache Administration User for Cache Operations in the Oracle TimesTen In-Memory Database Cache Guide.

Perform these steps to run the grantCacheAdminPrivileges.sql script:

Create a shell from which you can access your Oracle Database, and then from the directory of your choice, create an empty subdirectory. This example creates the oraclescripts subdirectory.
```
mkdir -p oraclescripts
```
From your Linux development host, use the kubectl cp command to copy the grantCacheAdminPrivileges.sql script from the installation_dir/oraclescripts directory on your Linux development host to the oraclescripts directory that you just created.
Note:
You must unpack the TimesTen distribution to retrieve the installation_dir/oraclescripts/grantCacheAdminPrivileges.sql directory. See Unpack the TimesTen and the TimesTen Operator Distributions.
```
kubectl cp /installation_dir/oraclescripts/grantCacheAdminPrivileges.sql 
database-oracle:oraclescripts/grantCacheAdminPrivileges.sql
```
(Optional): From your shell, verify the script is located in the oraclescripts directory.
```
ls oraclescripts
```
The output is the following.
```
grantCacheAdminPrivileges.sql
```

Change to the database-oracle:oraclescripts directory. Next, use SQL*Plus to connect to the Oracle Database as the sys user. Next, run the grantCacheAdminPrivileges.sql script.

cd oraclescripts

sqlplus sys/syspwd@oracache as sysdba

In SQL*Plus, run the SQL script.

@grantCacheAdminPrivileges "cacheuser_grid";

The output is similar to the following.

Please enter the administrator user id
The value chosen for administrator user id is cacheuser_grid

TT_CACHE_ADMIN_ROLE role already exists
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER_GRID
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER_GRID
2. Granting the DBMS_LOCK package privilege to CACHEUSER_GRID
3. Granting the DBMS_DDL package privilege to CACHEUSER_GRID
4. Granting the DBMS_FLASHBACK package privilege to CACHEUSER_GRID
5. Granting the CREATE SEQUENCE privilege to CACHEUSER_GRID
6. Granting the CREATE CLUSTER privilege to CACHEUSER_GRID
7. Granting the CREATE OPERATOR privilege to CACHEUSER_GRID
8. Granting the CREATE INDEXTYPE privilege to CACHEUSER_GRID
9. Granting the CREATE TABLE privilege to CACHEUSER_GRID
10. Granting the CREATE PROCEDURE  privilege to CACHEUSER_GRID
11. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER_GRID
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEUSER_GRID
13. Granting the DBMS_LOB package privilege to CACHEUSER_GRID
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER_GRID
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER_GRID
16. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
18. Granting the CREATE TYPE privilege to CACHEUSER_GRID
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEUSER_GRID
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEUSER_GRID
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEUSER_GRID
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEUSER_GRID
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEUSER_GRID
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEUSER_GRID
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEUSER_GRID
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEUSER_GRID
(optional)
27. Granting the SELECT on SYS.GV$PROCESS privilege  to CACHEUSER_GRID
(optional)
28. Granting the SELECT ANY TRANSACTION privilege to CACHEUSER_GRID
29. Creating the CACHEUSER_GRID.TT_07_ARDL_CG_COUNTER table
30. Granting SELECT privilege on CACHEUSER_GRID.TT_07_ARDL_CG_COUNTER table to
PUBLIC
********* Initialization for cache admin user done successfully *********

Exit from SQL*Plus and the shell.

exit;

exit;

You successfully ran the script to grant privileges to the cache administration user.

Create the Oracle Database Table to Be Cached

This example creates the readtab table in the oratt_grid user schema. Later, the table is cached in a read-only cache group.

Create a shell from which you can access your Oracle Database and then use SQL*Plus to connect to the Oracle Database as the sys user. Then create the oratt_grid.readtab table.
```
sqlplus sys/syspwd@oracache as sysdba
```
```
CREATE TABLE oratt_grid.readtab (keyval NUMBER NOT NULL PRIMARY KEY,str VARCHAR2(32));
```
The output is the following.
```
Table created.
```

Use SQL*Plus to insert rows into the oratt_grid.readtab table. Next, verify the rows have been inserted.

INSERT INTO oratt_grid.readtab VALUES (1,'Hello');

INSERT INTO oratt_grid.readtab VALUES (2,'World');

Commit.

commit;

Verify the rows have been inserted into the tables.

SELECT * FROM oratt_grid.readtab;

The output is the following.

KEYVAL STR
---------- --------------------------------
         1 Hello
         2 World

Use SQL*Plus to grant the SELECT privilege on the oratt_grid.readtab table.
```
GRANT SELECT ON oratt_grid.readtab TO cacheuser_grid;
```
The output is the following.
```
Grant succeeded.
```
Use SQL*Plus to query the nls_database_parameters system view to determine the Oracle Database database character set. The Oracle Database database character set must match the TimesTen database character set.
In this example, the query returns the AL32UTF8 database character set.
```
 SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
```
The output is the following.
```
VALUE
--------------------------------------------------------------------------------
AL32UTF8
```
Exit from SQL*Plus and the shell.
```
exit;
```
```
exit;
```

You successfully created the Oracle Database table. Later, this table is cached in the TimesTen cache group.