Tracking DDL Statements Issued on Cached Oracle Database Tables

When a DDL statement is issued on a cached Oracle Database table, this statement can be tracked in the Oracle Database TT_version_DDL_L table when the Oracle Database TT_version_schema-ID_DDL_T trigger is fired to insert a row into the table. The version is an internal TimesTen version number and schema-ID is the ID of user that owns the cached Oracle Database table.

A trigger is created for each Oracle Database user that owns cached Oracle Database tables. One DDL tracking table is created to store DDL statements issued on any cached Oracle Database table. The Oracle cache administration user owns the TT_version_DDL_L table and the TT_version_schema-ID_DDL_T trigger.

By default, DDL statements are not tracked. On TimesTen, you can enable tracking of DDL statements issued on cached Oracle Database tables, call the ttCacheDDLTrackingConfig built-in procedure as the TimesTen cache administration user. The following example enables tracking of DDL statements issued on cached Oracle Database tables:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheDDLTrackingConfig('enable');

The TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger are automatically created if the Oracle cache administration user has been granted the set of required privileges including CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE, CREATE TABLE and CREATE ANY TRIGGER. These Oracle Database objects are created when you create a cache group after tracking of DDL statements has been enabled.

On TimesTen Classic, if you manually created the Oracle Database objects used to manage the caching of Oracle Database data, you need to run the ttIsql utility cachesqlget command with the ORACLE_DDL_TRACKING option and the INSTALL flag as the TimesTen cache administration user. This command should be run for each Oracle Database user that owns cached Oracle Database tables that you want to track DDL statements on. Running this command generates a SQL*Plus script used to create the TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger in the Oracle database.

After generating the script, use SQL*Plus to run the script as the sys user.

The following example creates DDL tracking table and trigger when Oracle Database objects are manually created. In this example, the SQL*Plus script generated by the ttIsql utility cachesqlget command is saved to the /tmp/trackddl.sql file. The owner of the cached Oracle Database table sales is passed as an argument to the command.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachesqlget ORACLE_DDL_TRACKING sales INSTALL /tmp/trackddl.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/trackddl
SQL> exit

You can run the ttCacheInfo utility or the timesten_home/install/oraclescripts/cacheInfo.sql SQL*Plus script as the Oracle cache administration user to display information about the Oracle Database objects used to track DDL statements issued on cached Oracle Database tables. The following example runs the cacheInfo.sql SQL*Plus script.

% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheInfo.sql
***************** Database Information	*********************
Database name: DATABASE1
Unique database name: database1
Primary database name:
Database Role: PRIMARY
Database Open Mode: READ WRITE
Database Protection Mode: MAXIMUM PERFORMANCE
Database Protection Level: UNPROTECTED
Database Flashback On: NO
Database Current SCN: 21512609
*************************************************************
*************Autorefresh Objects Information  ***************
Grid name: grid1 (7D03C680-BD93-4233-A4CF-B0EDB0064F3F)
Timesten database name: database1
Cache table name: SALES.CUSTOMERS
Change log table name: tt_07_96977_L
Number of rows in change log table: 4
Maximum logseq on the change log table: 1
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
*************DDL Tracking Object Information  ***************
Common DDL Log Table Name: TT_07_DDL_L
DDL Trigger Name: TT_07_315_DDL_T
Schema for which DDL Trigger is tracking: SALES
Number of cache groups using the DDL Trigger: 10
****************************
 
PL/SQL procedure successfully completed.

The information returned for each Oracle Database user that owns cached Oracle Database tables includes the name of the DDL tracking table, the name of its corresponding DDL trigger, the name of the user that the DDL trigger is associated with, and the number of cache groups that cache a table owned by the user associated with the DDL trigger.

If a cache group contains more than one cache table, each cache table owned by the user associated with the DDL trigger contributes to the cache group count.

See SQL*Plus Scripts for Cache in this book and ttCacheDDLTrackingConfig and ttCacheInfo in Oracle TimesTen In-Memory Database Reference.