Create the Oracle Database Users and Default Tablespace

Create a default tablespace to store meta information about cache operations. Create a cache administration user that creates, owns, and maintains Oracle database objects that store information used to manage the cache environment for a TimesTen database and enforce predefined behaviors of particular cache group types.

Perform the following on the Oracle database:

Note:

If you are using Oracle Autonomous Database Serverless for the Transaction Processing workload type, use the preconfigured databases services LOW or TP to avoid queries being automatically executed in parallel. In addition, if you are using a multitenant container database (CDB) or pluggable database (PDB), note the specific instructions below on how to create the cache administration user and grant this user privileges in a CDB or PDB.

1. Create a default tablespace that stores information about cache operations.

   This tablespace is used for storing cache management objects that should not be shared with other applications. While you may also store Oracle database tables that are cached in a TimesTen database, we strongly recommend that this tablespace be used solely by the TimesTen database for cache management.

   See Managing a Cache Environment with Oracle Database Objects for a list of Oracle database tables used by the cache administration user.

   In the following SQL*Plus example, the default tablespace is cachetblsp and defines a 5 GB data file named tt_cache.f. Choose a size that is appropriate for your particular needs. Provide the SEGMENT SPACE MANAGEMENT AUTO clause so that the Oracle database automatically manages the free space of all segments in the tablespace (useful for monitoring autorefresh).

   Note:

   The use of the sys@tnsservicename as sysdba user in this example is applicable only for a test environment.

   % cd timesten_home/install/oraclescripts
   % sqlplus sys@tnsservicename as sysdba
   Enter password: password

   This example uses the sys@tnsservicename as sysdba user since the sys@tnsservicename user is able to grant the required privileges. For the Transaction Processing workload type, use the admin user instead. You can use any Oracle database user that has the appropriate privileges. See Required Privileges for Cache Administration User for Cache Operations.

   For the non-autonomous Oracle Database, use SQL*Plus to create a default tablespace. In the following example, the name of the default tablespace is cachetblsp:

   SQL> CREATE TABLESPACE cachetblsp DATAFILE 'tt_cache.f' SIZE 5G 
    SEGMENT SPACE MANAGEMENT AUTO;
   
   Tablespace created.

   Skip this step for the Transaction Processing workload type. Oracle Autonomous Database automatically configures default data and temporary tablespaces for the database. Adding, removing, or modifying tablespaces is not allowed. Oracle Autonomous Database creates one or multiple tablespaces automatically depending on the storage size.

2. Create an Oracle cache administration user that creates, owns, and maintains Oracle database objects that store information used to manage the cache environment for a TimesTen database and enforce predefined behaviors of particular cache group types.

   If you are using a multitenant container database (CDB) or pluggable database (PDB), the Oracle cache administrator user can be one of the following:

   • Local user: A local user is a database user that can operate only within a single PDB. You must assign cache privileges only within the PDB in which this user exists.

   • Common user: A common user is a database user known in every container and has the same identity in the CBD root and in every existing and future PDB in the CDB. You must assign cache privileges within each PDB in the CDB in which you want to use cache.

   Note:

   Each TimesTen database can be managed by only a single cache administration user on the Oracle database. However, a single cache administration user can manage multiple TimesTen databases. You can specify one or more cache administration users where each manages one or more TimesTen databases.

   See Caching the Same Oracle Table on Two or More TimesTen Databases.

   Designate the tablespace as the default tablespace for the Oracle cache administration user. This user creates tables in this tablespace that are used to store information about the cache environment and its cache groups. Other Oracle database objects (such change log tables, replication metadata tables, and triggers) are used to enforce the predefined behaviors of cache groups with autorefresh and AWT cache groups are created in the same tablespace. To create and manage these objects, the Oracle cache administration user must have a high level of privileges. A cache group with autorefresh refers to a read-only cache group or a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute.

   See Managing a Cache Environment with Oracle Database Objects for a list of Oracle Database tables and triggers owned by the cache administration user.

   Note:

   If you create multiple cache administration users, each may use the same or different tablespace as their default tablespace.

   As the sys user, use SQL*Plus to create the Oracle database cache administration user cacheadmin. In the example below, the default tablespace for the cacheadmin user is cachetblsp.

   For the non-autonomous Oracle Database, the following SQL*Plus example creates the cache administration user:

   SQL> CREATE USER cacheadmin IDENTIFIED BY orapwd
       DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

   For the Transaction Processing workload type, the following SQL*Plus example creates the cache administration user:

   SQL> CREATE USER cacheadmin IDENTIFYED BY orapwd
       QUOTA UNLIMITED ON DATA;

3. Identify one or more existing schemas (or create a new schema) with schema owners that own Oracle database tables that are to be cached in a TimesTen database. The tables to be cached may or may not already exist.