Set Up the Oracle Database to Cache Data

The following sections describe the tasks that must be performed in the Oracle Database:

Create the Oracle Database Users

Before you can use TimesTen Cache, you must 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 for information.

This example creates the cacheuser2 cache administration user and the oratt 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 to store the TimesTen Cache management objects. See Create the Oracle Database Users and Default Tablespace in the Oracle TimesTen In-Memory Database Cache Guide for information.
This example creates the cachetablespace2 tablespace.
```
% sqlplus sys/syspwd@oracache as sysdba

SQL> CREATE TABLESPACE cachetablespace2 DATAFILE 'datatt2.dbf' SIZE 100M;
 
Tablespace created.
```
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 schema user.
```
SQL> CREATE USER oratt IDENTIFIED BY oraclepwd;
 
User created.
 
SQL> GRANT CREATE SESSION, RESOURCE TO oratt;
 
Grant succeeded.
```
Use SQL*Plus to create the cache administration user. Assign the cachetablespace2 tablespace to this user. You will later use the same name of this Oracle cache administration user in the cacheUser metadata file.
This example creates the cacheuser2 user.
```
SQL> CREATE USER cacheuser2 IDENTIFIED BY oraclepwd 
       DEFAULT TABLESPACE cachetablespace2 
       QUOTA UNLIMITED ON cachetablespace2;
 
User created.

SQL> commit;
 
Commit complete.

SQL> exit
```

Grant Privileges to the Cache Administration User

The cache administration user must be granted a specific set of privileges depending on the cache group types that will be created in the TimesTen databases and the operations performed on those cache groups. TimesTen provides the grantCacheAdminPrivileges.sql SQL*Plus script that you can run 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. Recall that the installation_dir directory was created when you unpacked the TimesTen distribution. See Unpack the TimesTen and the TimesTen Operator Distributions for information on unpacking the TimesTen distribution.
```
% cp /installation_dir/oraclescripts/grantCacheAdminPrivileges.sql 
database-oracle:oraclescripts/grantCacheAdminPrivileges.sql
```
From your shell, verify the script is located in the oraclescripts directory.
```
% ls oraclescripts
grantCacheAdminPrivileges.sql
```

Use SQL*Plus to connect to the Oracle Database as the sys user. Then, run the oraclescripts/grantCacheAdminPrivileges.sql script. This script grants the cacheuser2 cache administration user the minimum set of privileges required to perform cache group operations. See Grant Privileges to the Oracle Cache Administration User in the Oracle TimesTen In-Memory Database Cache Guide for more information.

% sqlplus sys/syspwd@oracache as sysdba

SQL> @grantCacheAdminPrivileges "cacheuser2";
 
Please enter the administrator user id
The value chosen for administrator user id is cacheuser2
 
TT_CACHE_ADMIN_ROLE role already exists
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER2
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER2
2. Granting the DBMS_LOCK package privilege to CACHEUSER2
3. Granting the DBMS_DDL package privilege to CACHEUSER2
4. Granting the DBMS_FLASHBACK package privilege to CACHEUSER2
5. Granting the CREATE SEQUENCE privilege to CACHEUSER2
6. Granting the CREATE CLUSTER privilege to CACHEUSER2
7. Granting the CREATE OPERATOR privilege to CACHEUSER2
8. Granting the CREATE INDEXTYPE privilege to CACHEUSER2
9. Granting the CREATE TABLE privilege to CACHEUSER2
10. Granting the CREATE PROCEDURE  privilege to CACHEUSER2
11. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER2
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEUSER2
13. Granting the DBMS_LOB package privilege to CACHEUSER2
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER2
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER2
16. Checking if the cache administrator user has permissions on the 
    default tablespace
    Permission exists
18. Granting the CREATE TYPE privilege to CACHEUSER2
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEUSER2
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEUSER2
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEUSER2
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEUSER2
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEUSER2
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEUSER2
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEUSER2
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEUSER2 (optional)
27. Granting the SELECT ANY TRANSACTION privilege to CACHEUSER2
********* Initialization for cache admin user done successfully *********

You have successfully run the grantCacheAdminPrivileges.sql script in the Oracle Database.

Create the Oracle Database Tables to Be Cached

This example creates two tables in the oratt user schema. See "Create the Oracle Database Users" for information on this user.

readtab: This table will later be cached in a read-only cache group.
writetab: This table will later be cached in an AWT 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.readtab and the oratt.writetab tables.

% sqlplus sys/syspwd@oracache as sysdba

SQL> CREATE TABLE oratt.readtab (keyval NUMBER NOT NULL PRIMARY KEY, 
       str VARCHAR2(32));
 
Table created.

SQL> CREATE TABLE oratt.writetab (pk NUMBER NOT NULL PRIMARY KEY, 
       attr VARCHAR2(40));
 
Table created.

Use SQL*Plus to insert rows into the oratt.readtab and the oratt.writetab tables. Then verify the rows have been inserted.

SQL> INSERT INTO oratt.readtab VALUES (1,'Hello');
 
1 row created.

SQL> INSERT INTO oratt.readtab VALUES (2,'World');
 
1 row created.

SQL> INSERT INTO oratt.writetab VALUES (100, 'TimesTen');
 
1 row created.

SQL> INSERT INTO oratt.writetab VALUES (101, 'Cache');
 
1 row created.

SQL> commit;
 
Commit complete.

Verify the rows have been inserted into the tables.

SQL> SELECT * FROM oratt.readtab;
 
    KEYVAL STR
---------- --------------------------------
         1 Hello
         2 World
 
SQL>  SELECT * FROM oratt.writetab;
 
        PK ATTR
---------- ----------------------------------------
       100 TimesTen
       101 Cache

Use SQL*Plus to grant the SELECT privilege on the oratt.readtab table and the SELECT, INSERT, UPDATE, and DELETE privileges on the oratt.writetab table to the cache administration user (cacheuser2, in this example).

SQL> GRANT SELECT ON oratt.readtab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT SELECT ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT INSERT ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT UPDATE ON oratt.writetab TO cacheuser2;
 
Grant succeeded.
 
SQL> GRANT DELETE ON oratt.writetab TO cacheuser2;
 
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. (The TimesTen database character set will be set later. See "Create Metadata Files and a Kubernetes Facility" for details.)
In this example, the query returns the AL32UTF8 database character set.
```
SQL> SELECT value FROM nls_database_parameters WHERE 
       parameter='NLS_CHARACTERSET';
 
VALUE
------------------------------------------------------------------------------
AL32UTF8
```

You have successfully created the Oracle Database tables that will be cached in the TimesTen cache group tables.