6
Cache Management API

Oracle Database Cache provides a cache management API that allows you to manage caches using scripts or a utility such as SQL*Plus. The API consists of a PL/SQL package, DBMS_ICACHE, that is loaded into the cache during installation. This package provides the same functionality as that provided by Cache Manager. In addition, it allows you to:

Disable routing of PL/SQL subprograms (procedures and functions) and anonymous blocks without disabling routing of standard SQL queries.
Set intervals for statistics gathering and enable and disable statistics gathering

The package is installed in the SYS schema during the Oracle Database Cache installation.

The following tables provide brief descriptions of each PL/SQL procedure and function. Full descriptions of each procedure and function are provided later in this chapter.

The following table lists the PL/SQL functions and procedures that you can use to pause and restart caching, enable routing of PL/SQL procedures, enable gathering of statistics, control the interval at which statistics are gathered, and reset statistics:

Name Description

GET_FLAG

Indicates whether or not PL/SQL is routed to the cache or whether or not statistics are being collected.

GET_ROUTING_STATE

Indicates whether the cache is running or paused.

GET_STATS_INTERVAL

Returns the interval at which the specified type of statistics is collected.

GET_STATS_RESET_TIME

Returns the date and time that the specified statistics were last reset.

RESET_STATS

Resets the specified type of statistics to zero.

SET_FLAG

Enables or disables routing of PL/SQL calls to the cache or enables or disables the gathering of statistics.

SET_ROUTING_STATE

Starts a paused cache or pauses a running cache.

SET_STATS_INTERVAL

Sets the interval for the specified type of statistics collection.

Name	Description
GET_FLAG	Indicates whether or not PL/SQL is routed to the cache or whether or not statistics are being collected.
GET_ROUTING_STATE	Indicates whether the cache is running or paused.
GET_STATS_INTERVAL	Returns the interval at which the specified type of statistics is collected.
GET_STATS_RESET_TIME	Returns the date and time that the specified statistics were last reset.
RESET_STATS	Resets the specified type of statistics to zero.
SET_FLAG	Enables or disables routing of PL/SQL calls to the cache or enables or disables the gathering of statistics.
SET_ROUTING_STATE	Starts a paused cache or pauses a running cache.
SET_STATS_INTERVAL	Sets the interval for the specified type of statistics collection.

The following table lists the PL/SQL functions and procedures that you can use to add objects to or remove objects from the cache. The objects include tables and PL/SQL packages, procedures, and functions:

Name Description

ADD_PLSQL_OBJECT

Adds the named PL/SQL object (package, procedure, or function) to the cache.

ADD_TABLE

Adds the named table, its data, and its dependent objects to the cache and sets the synchronization policy of the table.

GET_TABLE_SIZE

Calculates the amount of disk space required to cache the named table.

REMOVE_PLSQL_OBJECT

Removes the named PL/SQL object from the cache.

REMOVE_TABLE

Removes the named table, its data, and its dependent objects from the cache.

SET_ORIGIN_CREDENTIALS

Sets the credentials to be used on the origin database.

SET_SYNC_METHOD

Modifies the synchronization method of the named table.

SET_SYNC_SCHEDULE

Modifies the synchronization schedule of the named table.

SYNC_PLSQL_OBJECT

Synchronizes the named PL/SQL object with the PL/SQL object on the origin database.

SYNC_TABLE

Synchronizes the named table with the table on the origin database.

SYNC_USERS

Synchronizes the list of users on the cache with the list of users on the origin database.

Name	Description
ADD_PLSQL_OBJECT	Adds the named PL/SQL object (package, procedure, or function) to the cache.
ADD_TABLE	Adds the named table, its data, and its dependent objects to the cache and sets the synchronization policy of the table.
GET_TABLE_SIZE	Calculates the amount of disk space required to cache the named table.
REMOVE_PLSQL_OBJECT	Removes the named PL/SQL object from the cache.
REMOVE_TABLE	Removes the named table, its data, and its dependent objects from the cache.
SET_ORIGIN_CREDENTIALS	Sets the credentials to be used on the origin database.
SET_SYNC_METHOD	Modifies the synchronization method of the named table.
SET_SYNC_SCHEDULE	Modifies the synchronization schedule of the named table.
SYNC_PLSQL_OBJECT	Synchronizes the named PL/SQL object with the PL/SQL object on the origin database.
SYNC_TABLE	Synchronizes the named table with the table on the origin database.
SYNC_USERS	Synchronizes the list of users on the cache with the list of users on the origin database.

Privileges Required

To use the PL/SQL package, you connect to the cache as a user with DBA privileges. Some procedures, such as DBMS_ICACHE.ADD_TABLE, require access to the origin database. Before you call those procedures, you must call the DBMS_ICACHE.SET_ORIGIN_CREDENTIALS procedure. See "SET_ORIGIN_CREDENTIALS Procedure" for more information.

Errors

The PL/SQL procedures and functions in this package raise the application error ORA-20001, following by the number and text of an Oracle Database Cache error message. For example, if you attempt to remove a table from the cache, but the table is not cached, you receive the following error:

ORA-20001: WTE-03806 Object not cached.
ORA-06512: at "SYS.DBMS_ICACHE", line 1227
ORA-06512: at line 1

See Appendix B for a description of the Oracle Database Cache errors.

For information about handling errors returned by PL/SQL procedures, see the PL/SQL User's Guide and Reference.

ADD_PLSQL_OBJECT Procedure

This procedure adds the named PL/SQL object (package, procedure, or function) to the cache. It also adds the associated object privileges and any synonyms to the cache.

Format

DBMS_ICACHE.ADD_PLSQL_OBJECT (
      schema_name IN VARCHAR2,
      object_name IN VARCHAR2 );

Parameters

schema_name

The name of the schema on the origin database that contains the PL/SQL object to be cached.

object_name

The name of the PL/SQL object to be cached.

Usage Notes

To execute this procedure, you must connect to the cache using AS SYSDBA in the connect statement.
You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
If you add a PL/SQL object that refers to an object, such as a table, that is not cached, you cannot cache the PL/SQL object. To correct the problem, cache the object to which the PL/SQL object refers, then add the PL/SQL object to the cache.
You should cache only PL/SQL objects that contain read-only statements. Insert, update, or delete statements are routed to the origin database.

Examples

Assume that you have a PL/SQL procedure called GET_TOY_ITEMS in the schema ICPB. Because this procedure contains only queries, it is a good candidate for caching. To cache the procedure, take the following steps:

Make sure that any tables that are referenced by the GET_TOY_ITEMS procedure are already cached.
Connect to the cache using a utility such as SQL*Plus. Connect as a user with SYSDBA privileges:
```
connect system/manager @mycache.us.oracle.com AS SYSDBA;
```
Because the DBMS_ICACHE.ADD_PLSQL_OBJECT procedure must connect to the origin database, you must pass user credentials to the origin database. Execute the DBMS_ICACHE.SET_ORIGIN_CREDENTIALS procedure, specifying the user name and password of a user on the origin database who has DBA privileges:
```
execute DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ('system','manager');
```
Execute the DBMS_ICACHE.ADD_PLSQL_OBJECT procedure, specifying the schema name and the procedure name:
```
execute DBMS_ICACHE.ADD_PLSQL_OBJECT ('ICPB', -
            'GET_TOY_ITEMS');
```
To verify that the procedure is cached, use the DESCRIBE statement:
```
describe ICPB.GET_TOY_ITEMS
```

ADD_TABLE Procedure

This procedure adds the specified table, its data, and its dependent objects to the cache. It also sets the synchronization policy of the table.

Format

DBMS_ICACHE.ADD_TABLE(
     schema_name IN VARCHAR2,
     table_name IN VARCHAR2,
     sync_method IN NUMBER,
     auto_refresh IN BOOLEAN,
     start_date IN DATE,
     interval IN INTEGER,
     build_indexes IN BOOLEAN,
     column_list IN VARCHAR2,
     exclusion_list IN VARCHAR2 );

Parameters

schema_name

The name of the schema that contains the table to be cached.

table_name

The name of the table to be cached.

sync_method

The synchronization method to be used for the table. Possible values are the following constants:

SYNC_INCREMENTAL: Synchronizes the data by refreshing only the data that has changed since the table was cached or since it was last synchronized.
SYNC_COMPLETE: Synchronizes the data by completely refreshing the table. When you select this method, Oracle Database Cache deletes all data in the table and replaces it with the current data from the origin database.

The default is SYNC_COMPLETE.

auto_refresh

A flag to indicate whether the table should be synchronized automatically on a given schedule or synchronized explicitly. To specify that the synchronization be performed automatically, use the value TRUE. To specify that the synchronization be performed only when it is explicitly initiated, use the value FALSE.

The default is FALSE.

start_date

The date and time to start the automatic synchronization. If you specify TRUE for auto_refresh, but you do not specify a start_date, the start_date is set to the value SYSDATE.

The default is NULL.

For information about date and time formats, see the Oracle8i SQL Reference.

interval

The interval, in seconds, between automatic synchronization. If you specify TRUE for auto_refresh, but you do not specify an interval, Oracle Database Cache returns the error "Automatic refresh interval is not provided."

build_indexes

This parameter is currently ignored.

column_list

This parameter is currently ignored.

exclusion_list

This parameter is currently ignored.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.

Examples

The following example adds the TOY_ITEMS table from the schema STOCK to the cache. It specifies an incremental, scheduled synchronization.

! Change the default date format to specify hours, minutes, seconds.
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
execute DBMS_ICACHE.ADD_TABLE ('ICPB', -
        'TOY_ITEMS', -
         DBMS_ICACHE.SYNC_INCREMENTAL, -
         TRUE, - 
         '04-OCT-2000 01:10:00', 3600*24);

GET_FLAG Function

This function returns a flag that indicates whether or not the routing of PL/SQL to the cache is enabled or whether or not the collection of statistics is enabled.

Format

DBMS_ICACHE.GET_FLAG (
     flag IN NUMBER )
   RETURN BOOLEAN;

Parameters

flag

The name of the flag to be retrieved. Possible values are the following constants:

PLSQL_ROUTING_ENABLED: Whether or not the routing of PL/SQL subprograms (procedures and functions) and anonymous blocks to the cache is enabled
STATS_REPORTING_ENABLED: Whether or not the collection of statistics is enabled

Return Value

Returns TRUE if the specified flag is enabled. Returns FALSE if the specified flag is disabled.

Examples

The following example uses the GET_FLAG function to check whether or not PL/SQL subprograms are routed to the cache. If they are, it disables routing of PL/SQL.

begin
  if DBMS_ICACHE.GET_FLAG (DBMS_ICACHE.PLSQL_ROUTING_ENABLED)
     then DBMS_ICACHE.SET_FLAG(DBMS_ICACHE.PLSQL_ROUTING_ENABLED, FALSE);
  end if;
end;

GET_ROUTING_STATE Function

This function returns a constant that indicates whether the cache is running or paused.

Format

DBMS_ICACHE.GET_ROUTING_STATE
   RETURN NUMBER;

Return Value

Returns the constant RUNNING if the cache is running. If the cache is running, queries are routed to the cache. You can perform all operations on the cache, and all statistics are gathered.

Returns the constant PAUSED if the cache is paused. If the cache is paused, the cache is running, but queries are routed directly to the origin database. If a cache is paused, you can view all information about the cache. You can add and remove tables and synchronize a paused cache. Some statistics are gathered while the cache is paused, but the Hit/Miss Percentage and Hit/Miss Count per Second statistics are not gathered.

Examples

The following example uses the GET_ROUTING_STATE procedure to check if the cache is paused. If it is, the example calls the procedure SET_ROUTING_STATE to start the paused cache.

begin
  if DBMS_ICACHE.GET_ROUTING_STATE ( ) = DBMS_ICACHE.PAUSED
     then DBMS_ICACHE.SET_ROUTING_STATE (DBMS_ICACHE.RUNNING);
  end if;
end;

GET_STATS_INTERVAL Function

This function returns the interval, in seconds, at which a specified type of statistics is collected.

Format

DBMS_ICACHE.GET_STATS_INTERVAL (
     interval IN NUMBER )
   RETURN NUMBER;

Parameters

interval

The type of statistics collection for which the interval is to be retrieved. Possible values are the following constants:

AGGREGATE_STATS: The interval at which clients report to the cache the number of hits, misses, and other queries they have executed.
QUERY_STATS: The interval at which clients report to the cache the queries they have executed, their status, and the hit miss counts.
STATS_COMPILATION: The interval at which the cache compiles the query statistics (QUERY_STATS) reported by all clients.

Return Value

The interval, in seconds, for the specified type.

Examples

The following example returns the interval for QUERY_STATS to the screen in SQL*Plus:

set serveroutput on
execute DBMS_OUTPUT.PUT_LINE -
     (DBMS_ICACHE.GET_STATS_INTERVAL(DBMS_ICACHE.QUERY_STATS));

GET_STATS_RESET_TIME Function

This function returns the date and time that the specified statistics were last reset.

Format

DBMS_ICACHE.GET_STATS_RESET_TIME (
      category IN NUMBER)
    RETURN DATE;

Parameters

category

The category of statistics collection for which you want the last reset time returned. Possible values are the following constants:

AGGREGATE_COUNTERS: The number of hits, misses, and other queries executed by the clients. If you specify this value, the function returns the last time the number of hits, misses, and other queries was reset to zero.

These statistics can be viewed on the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart.
QUERY_COUNTERS: The number of hits and misses for each query. If you specify this value, the function returns the last time the number of hits and misses per query was reset to zero.

This reset time and these statistics can viewed on the Hit/Miss Statistics property page.
QUERY_ENTRIES: All queries executed by the clients. If you specify this value, the function returns the last time the queries were deleted from the statistics view, DBA_ICACHE_QUERY_STATS, in the cache.

The queries can be viewed on the Hit/Miss Statistics property page.

Return Value

Returns the date and time that the specified type of statistics was reset to zero or deleted.

Examples

The following example returns the last reset time for AGGREGATE_COUNTERS to the screen in SQL*Plus:

set serveroutput on
execute DBMS_OUTPUT.PUT_LINE -
  (DBMS_ICACHE.GET_STATS_RESET_TIME (DBMS_ICACHE.AGGREGATE_COUNTERS));

GET_TABLE_SIZE Procedure

This procedure calculates the amount of disk space required to cache the named table. It calculates the size of the table and its dependent objects. Use this procedure to ensure that sufficient disk space exists before you add a table to the cache.

Format

DBMS_ICACHE.GET_TABLE_SIZE (
     schema_name IN VARCHAR2,
     table_name IN VARCHAR2,
     table_size OUT NUMBER );

Parameters

schema_name

The name of the schema that contains the table to be sized.

table_name

The name of the table to be sized.

table_size

The parameter to hold the size of the table, in megabytes (MB).

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.

Examples

The following example shows how to get the size for the SCOTT.EMP table:

declare tab_size number;
begin
DBMS_ICACHE.GET_TABLE_SIZE ('scott', 'emp', tab_size);
DBMS_OUTPUT.PUT_LINE:('Table size is:' || tab_size);
end;
/
Table size is: .0078125

REMOVE_PLSQL_OBJECT Procedure

This procedure removes the named PL/SQL object (package, procedure, or function) from the cache.

Format

DBMS_ICACHE.REMOVE_PLSQL_OBJECT (
      schema_name IN VARCHAR2,
      object_name IN VARCHAR2 );

Parameters

schema_name

The name of the schema that contains the PL/SQL object to be removed.

object_name

The name of the PL/SQL object to be removed from the cache.

Usage Note

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.

Examples

The following example removes the PL/SQL procedure GET_TOY_ITEMS in schema ICPB from the cache:

execute DBMS_ICACHE.REMOVE_PLSQL_OBJECT('ICPB','GET_TOY_ITEMS' );

REMOVE_TABLE Procedure

This procedure removes the named table, its data, and its dependent objects from the cache. If no other objects owned by the named schema are in the cache, Oracle Database Cache performs additional cleanup operations.

Format

DBMS_ICACHE.REMOVE_TABLE (
      schema_name IN VARCHAR2,
      table_name IN VARCHAR2 );

Parameters

schema_name

The name of the schema that contains the table to be removed.

table_name

The name of the table to be removed from the cache.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.

Examples

The following example removes the table ICPB.TOY_ITEMS from the cache:

execute DBMS_ICACHE.REMOVE_TABLE ('ICPB','TOY_ITEMS' );

RESET_STATS Procedure

This procedure resets the specified category of statistics.

Format

DBMS_ICACHE.RESET_STATS (
      category IN NUMBER );

Parameters

category

The category of statistics that you want to reset. Possible values are the following constants:

AGGREGATE_COUNTERS: The number of hits, misses, and other queries executed by the clients. If you specify this value, the number of hits, misses, and other queries is reset to zero.

These statistics can be viewed on the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart.
QUERY_COUNTERS: The number of hits and misses for each query. If you specify this value, the number of hits and misses per query is reset to zero.

These statistics can viewed on the Hit/Miss Statistics property page.
QUERY_ENTRIES: All queries executed by the clients. If you specify this value, the queries are deleted from the statistics view, DBA_ICACHE_QUERY_STATS, in the cache.

The queries can be viewed on the Hit/Miss Statistics property page.

Examples

The following example resets the AGGREGATE_COUNTERS to zero:

execute DBMS_ICACHE.RESET_STATS (DBMS_ICACHE.AGGREGATE_COUNTERS);

SET_FLAG Procedure

This procedure enables or disables the routing of PL/SQL subprograms (procedures and functions) and anonymous blocks to the cache or it enables or disables the collection of statistics.

Format

DBMS_ICACHE.SET_FLAG (
     name IN NUMBER,
     value IN BOOLEAN );

Parameters

name

The name of the flag to be set. Possible values are the following constants:

PLSQL_ROUTING_ENABLED: Whether to enable or disable the routing of PL/SQL subprograms and anonymous blocks to the cache.
STATS_REPORTING_ENABLED: Whether to enable or disable the collection of statistics.

value

The value to which to set the flag. To enable a flag, set the value to TRUE. To disable a flag, set the value to FALSE.

Examples

The following example enables the collection of statistics:

execute DBMS_ICACHE.SET_FLAG ( -
            DBMS_ICACHE.STATS_REPORTING_ENABLED, -
            TRUE );

The following example disables the routing of PL/SQL subprograms and anonymous blocks to the cache:

execute DBMS_ICACHE.SET_FLAG ( -
            DBMS_ICACHE.PLSQL_ROUTING_ENABLED, -
            FALSE );

SET_ORIGIN_CREDENTIALS Procedure

This procedure sets the user name and password to be used to authenticate the user as a DBA on the origin database. You must execute this procedure before you execute procedures that involve the origin database.

Format

DBMS_ICACHE.SET_ORIGIN_CREDENTIALS (
      username IN VARCHAR2,
      password IN VARCHAR2,
      connect_type IN INTEGER );

Parameters

username

The name of a user on the origin database who has DBA privileges.

password

The password associated with the specified user.

connect_type

The type of connection to be made to the origin database. Possible values are the following constants:

CONNECT_NORMAL: Connects to the origin database as the named user, with that user's privileges. This type of connection is sufficient to execute the Oracle Database Cache PL/SQL procedures and functions.
CONNECT_SYSDBA: Connects to the origin database as the named user, with SYSDBA privileges. If the user does not have SYSDBA privileges on the origin database, an error is returned.

The default is CONNECT_NORMAL.

Usage Notes

You must execute this procedure before you execute the following procedures, which involve the origin database:

Examples

The following example shows how to call this procedure from SQL*Plus. You must be connected to the cache as a user with DBA privileges.

! Set the credentials for the origin database.
execute DBMS_ICACHE.SET_ORIGIN_CREDENTIALS ('system','manager');

SET_ROUTING_STATE Procedure

This procedure starts a paused cache or pauses a running cache.

Format

DBMS_ICACHE.SET_ROUTING_STATE (
     state IN NUMBER );

Parameters

state

The state to which you want to set the cache. To start a paused cache, set the value to the constant RUNNING. To pause a running cache, set the value to the constant PAUSED.

Usage Notes

See "GET_ROUTING_STATE Function" for information about the meaning of RUNNING and PAUSED.
You cannot use PL/SQL to start a stopped cache or to stop a cache. To do either operation, see "Starting Oracle Database Cache and Related Software".

Examples

The following example pauses the cache:

execute DBMS_ICACHE.SET_ROUTING_STATE (DBMS_ICACHE.PAUSED );

SET_STATS_INTERVAL Procedure

This procedure sets the interval, in seconds, for the specified type of statistics collection.

Format

DBMS_ICACHE.SET_STATS_INTERVAL (
     interval IN NUMBER,
     seconds IN NUMBER );

Parameters

interval

The type of statistics collection for which the interval is to be set. Possible values are the following constants:

AGGREGATE_STATS: The interval at which clients report to the cache the number of hits, misses, and other queries they have executed.

This interval reflects the rate at which the Hit/Miss Percentages chart and the Hit/Miss Count per Second chart are refreshed. (The charts are on the Performance Monitor property page of Cache Manager.) By default, Oracle Database Cache sets this interval to 5 seconds. If you are viewing the charts in Cache Manager, the rate is lowered. When the charts are closed, the rate reverts to the specified rate.
QUERY_STATS: The interval at which clients report to the cache the queries they have executed, their status, and the hit miss counts. By default, Oracle Database Cache sets the interval to 60 seconds.
STATS_COMPILATION: The interval at which the cache compiles the query statistics (QUERY_STATS) that are reported by all clients.

This interval affects the rate at which the Hit/Miss Statistics property page in the Cache Manager is refreshed. By default, Oracle Database Cache sets the interval to 10 seconds. This interval should be equal to or less than the interval for QUERY_STATS.

seconds

The length of the interval, in seconds.

Examples

The following example sets the interval for collecting AGGREGATE_STATS to 10 seconds:

execute DBMS_ICACHE.SET_STATS_INTERVAL ( -
          DBMS_ICACHE.AGGREGATE_STATS, -
          10 );

SET_SYNC_METHOD Procedure

This procedure modifies the synchronization method of a table in the cache.

Format

DBMS_ICACHE.SET_SYNC_METHOD (
     schema_name IN VARCHAR2,
     table_name IN VARCHAR2,
     sync_method IN NUMBER );

Parameters

schema_name

The name of the schema that contains the table to be synchronized.

table_name

The name of the table to be synchronized.

sync_method

The type of synchronization to be used for the named table. Possible values are the following constants:

SYNC_INCREMENTAL: Synchronizes the data by refreshing only the data that has changed since the table was cached or since it was last synchronized.
SYNC_COMPLETE: Synchronizes the data by completely refreshing the table. When you select this method, Oracle Database Cache deletes all data in the table and replaces it with the current data from the origin database.

The default is SYNC_COMPLETE.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
This procedure does not change whether the synchronization is scheduled or unscheduled. To do that, use the SET_SYNC_SCHEDULE procedure.

Examples

The following example changes the synchronization method of ICPB.TOY_ITEMS to complete synchronization.

execute DBMS_ICACHE.SET_SYNC_METHOD ('ICPB', -
            'TOY_ITEMS', - 
            DBMS_ICACHE.SYNC_COMPLETE);

SET_SYNC_SCHEDULE Procedure

This procedure modifies the synchronization schedule for a table in the cache. You can specify whether or not you want to schedule the table for automatic synchronization and, if so, the start time and interval for the synchronization.

Format

DBMS_ICACHE.SET_SYNC_SCHEDULE (
     schema_name IN VARCHAR2,
     table_name IN VARCHAR2,
     auto_refresh IN BOOLEAN,
     start_date IN DATE,
     interval IN INTEGER );

Parameters

schema_name

The name of the schema that contains the named table.

table_name

The name of the table for which you want to modify the synchronization schedule.

auto_refresh

The default is FALSE.

start_date

The date and time to start the automatic synchronization. If you specify TRUE for auto_refresh, you must specify a start_date for the synchronization to be performed.

The default is NULL.

For information about date and time formats, see the Oracle8i SQL Reference.

interval

The interval, in seconds, between automatic synchronization of the table. If you specify TRUE for auto_refresh, you should specify an interval. If you do not, the synchronization will be performed only once, at the start_date.

The default is zero.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
This procedure does not change whether the synchronization is Complete or Incremental. To do that, use the SET_SYNC_METHOD procedure.

Examples

The following example changes the schedule of the synchronization of the table ICPB.TOY_ITEMS.

execute DBMS_ICACHE.SET_SYNC_SCHEDULE( 'ICPB', -
           'TOY_ITEMS', -
           TRUE, -
          '05-NOV-2000 02:00:00', -
           3600*24);

The following example changes the synchronization of the table ICPB.TOY_ITEMS to unscheduled.

execute DBMS_ICACHE.SET_SYNC_SCHEDULE( 'ICPB', -
           'TOY_ITEMS', -
           FALSE );

SYNC_PLSQL_OBJECT Procedure

This procedure initiates the immediate synchronization of a PL/SQL object. When you call this procedure, it synchronizes the definition (the metadata) of the specified PL/SQL object in the cache with the object in the origin database. Use this procedure when the definition of a PL/SQL object in the origin database is modified.

Format

DBMS_ICACHE.SYNC_PLSQL_OBJECT (
      schema_name IN VARCHAR2,
      object_name IN VARCHAR2 );

Parameters

schema_name

The name of the schema that contains the PL/SQL object to be synchronized.

object_name

The name of the PL/SQL object to be synchronized.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
You cannot set a synchronization policy or schedule for PL/SQL objects. You must manually initiate the synchronization using this procedure.

Examples

The following example updates the definition of the PL/SQL procedure GET_MUSIC_ITEMS:

execute DBMS_ICACHE.SYNC_PLSQL_OBJECT ('ICPB', -
            'GET_MUSIC_ITEMS');

SYNC_TABLE Procedure

This procedure initiates the immediate synchronization of a table. When you call this procedure, it synchronizes the specified table in the cache with the table in the origin database. Use this procedure when the synchronization policy of a table is not Scheduled. Even if the synchronization policy is Scheduled, you may decide that you want to initiate the action, rather than wait until the next scheduled synchronization.

Format

DBMS_ICACHE.SYNC_TABLE (
      schema_name IN VARCHAR2,
      table_name IN VARCHAR2 );

Parameters

schema_name

The name of the schema that contains the table to be synchronized.

table_name

The name of the table to be synchronized.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
If the synchronization policy of a table is Complete, this procedure performs a Complete synchronization on the specified table immediately. Similarly, if the synchronization policy of a table is Incremental, this procedure performs an Incremental synchronization immediately.

Examples

The following example synchronizes the table ICPB.TOY_ITEMS immediately:

execute DBMS_ICACHE.SYNC_TABLE ( 'ICPB', 'TOY_ITEMS');

SYNC_USERS Procedure

This procedure synchronizes the list of users on the cache with the list of users on the origin database. It updates the list of nonsystem users and the attributes of the users on the cache, so that it is consistent with the list of nonsystem users on the origin database.

Format

DBMS_ICACHE.SYNC_USERS ;

Parameters

None.

Usage Notes

You must execute the SET_ORIGIN_CREDENTIALS procedure before you execute this procedure.
You must synchronize the user list in the following circumstances:
- If a new user-defined database user has been added to the origin database, you must add that user to the cache before you can cache tables from that user's schema.
- If an attribute, such as the password, of an existing user is modified in the origin database, you must update the attribute in the cache. If you do not update the password, for example, applications invoked by that user will not be able to access data in the cache.
- If a user is dropped from the origin database, you must drop the user from the cache. If you do not, queries on tables in that user's schema continue to access the data in the cache. In addition, synchronization of the table may fail.

Examples

The following procedure synchronizes the user list:

execute DBMS_ICACHE.SYNC_USERS ;

6 Cache Management API

Privileges Required

Errors

ADD_PLSQL_OBJECT Procedure

Format

Parameters

Usage Notes

Examples

ADD_TABLE Procedure

Format

Parameters

Usage Notes

Examples

GET_FLAG Function

Format

Parameters

Return Value

Examples

GET_ROUTING_STATE Function

Format

Return Value

Examples

GET_STATS_INTERVAL Function

Format

Parameters

Return Value

Examples

GET_STATS_RESET_TIME Function

Format

Parameters

Return Value

Examples

GET_TABLE_SIZE Procedure

Format

Parameters

Usage Notes

Examples

REMOVE_PLSQL_OBJECT Procedure

Format

Parameters

Usage Note

Examples

REMOVE_TABLE Procedure

Format

Parameters

Usage Notes

Examples

RESET_STATS Procedure

Format

Parameters

Examples

SET_FLAG Procedure

Format

Parameters

Examples

SET_ORIGIN_CREDENTIALS Procedure

Format

Parameters

Usage Notes

Examples

SET_ROUTING_STATE Procedure

Format

Parameters

Usage Notes

Examples

SET_STATS_INTERVAL Procedure

Format

Parameters

Examples

SET_SYNC_METHOD Procedure

Format

Parameters

Usage Notes

Examples

SET_SYNC_SCHEDULE Procedure

Format

Parameters

Usage Notes

Examples

SYNC_PLSQL_OBJECT Procedure

6
Cache Management API