1. Cache Guide
  2. Managing a Caching Environment
  3. Dropping Oracle Database Objects Used by Cache Groups With Autorefresh

Dropping Oracle Database Objects Used by Cache Groups With Autorefresh

A TimesTen database is unavailable, for example, when the TimesTen system is taken offline or the database has been destroyed without dropping its cache groups with autorefresh.

Oracle database objects used to implement autorefresh operations also continue to exist in the Oracle database when a TimesTen database is no longer being used but still contains cache groups with autorefresh. Rows continue to accumulate in the change log tables. This impacts autorefresh performance on other TimesTen databases. Therefore, it is desirable to clean up these Oracle database objects associated with the unavailable or abandoned TimesTen database.

  • When using TimesTen Classic, run the timesten_home/install/oraclescripts/cacheCleanUp.sql SQL*Plus script as the Oracle cache administration user to clean up the Oracle database objects used to implement autorefresh operations. The host name of the TimesTen Classic system and the TimesTen database (including its path) are passed as arguments to the cacheCleanUp.sql script.

    You can run the ttCacheInfo utility or the cacheInfo.sql script as the Oracle cache administration user to determine the host and database names.

  • In TimesTen Scaleout, run the timesten_home/install/oraclescripts/scaleoutCacheCleanUp.sql SQL*Plus script as the Oracle cache administration user to clean up the Oracle Database objects used to implement autorefresh operations. The grid name and the TimesTen database name are passed as arguments to the scaleoutCacheCleanUp.sql script.

    You can run the cacheInfo.sql script as the Oracle cache administration user to determine the grid and database names.

The cacheInfo.sql script or the ttCacheInfo utility can be used to determine whether any objects used to implement autorefresh operations exist in the Oracle database.

The following example demonstrates how to drop Oracle database objects for cache groups with autorefresh. This example uses the cacheCleanUp.sql script for a TimesTen Classic system. It drops the change log tables and triggers associated with the customers and orders cache tables. The scaleoutCacheCleanup.sql script runs in the same manner for TimesTen Scaleout, except that it requires the grid name and database name as input parameters. 

% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheCleanUp "sys1" "/disk1/databases/database1"

*****************************OUTPUT**************************************
Performing cleanup for object_id: 69959 which belongs to table : CUSTOMERS
Executing: delete from tt_07_agent_status where host = sys1 and datastore =
/disk1/databases/database1 and object_id = 69959
Executing: drop table tt_07_69959_L
Executing: drop trigger tt_07_69959_T
Executing: delete from tt_07_user_count where object_id = object_id1
Performing cleanup for object_id: 69966 which belongs to table : ORDERS
Executing: delete from tt_07_agent_status where host = sys1 and datastore =
/disk1/databases/database1 and object_id = 69966
Executing: drop table tt_07_69966_L
Executing: drop trigger tt_07_69966_T
Executing: delete from tt_07_user_count where object_id = object_id1
**************************************************************************

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