Tracking DDL Statements Issued on Cached Oracle Database Tables
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.