This procedure is overloaded.

Syntax

DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id      IN NUMBER);

DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id_list IN DBMS_UTILITY.NUMBER_ARRAY);

Parameters

Usage Notes

If called after PREPARE_REFRESH, this procedure drops the outside tables created by it and unlocks the tables and materialized views in the sync refresh group.

If called after EXECUTE_REFRESH fails, this procedure restores the state of tables to before EXECUTE_REFRESH by undoing any partition exchanges which successfully finished.

This procedure releases the locks placed on the tables in the sync refresh group which were placed on them by the PREPARE_REFRESH procedure. See "PREPARE_REFRESH Procedure" for a description of these locks.

ABORT_REFRESH will work only if a PREPARE_REFRESH or EXECUTE_REFRESH statement has failed. It cannot be used after successful runs of those commands, and throws an error in such cases.

Syntax

DBMS_SYNC_REFRESH.ALTER_REFRESH_STATS_RETENTION (
   retention   IN NUMBER);

Parameters

This procedure lists all of the table's dependent materialized views and whether they qualify for sync refresh. Note that a materialized view may qualify for sync refresh even though the base table may not.

The eligibility rules for materialized views for synchronous refresh are discussed in detail in Oracle Database Data Warehousing Guide.

You can invoke CAN_SYNCREF_TABLE in two ways. The first is to use a table, while the second is to create a VARRAY.

Syntax

DBMS_SYNC_REFRESH.CAN_SYNCREF_TABLE (
    schema_name    IN VARCHAR2,
    table_name     IN VARCHAR2,
    statement_id   IN VARCHAR2);

DBMS_SYNC_REFRESH.CAN_SYNCREF_TABLE (
    schema_name    IN VARCHAR2,
    table_name     IN VARCHAR2,
    output_array   IN OUT Sys.CanSyncRefTypeArray);

Note that only one of statement_id or output_array need be provided to CAN_SYNCREF_TABLE.

Parameters

Using SYNCREF_TABLE

The output of CAN_SYNCREF_TABLE can be directed to a table named SYNCREF_TABLE. The user is responsible for creating the SYNCREF_TABLE; it can be dropped when it is no longer needed. Its structure is as follows:

CREATE TABLE SYNCREF_TABLE (
   statement_id    VARCHAR2(30),
   schema_name     VARCHAR2(30),
   table_name      VARCHAR2(30),
   mv_schema_name  VARCHAR2(30),
   mv_name         VARCHAR2(30),
   eligible        VARCHAR2(1),
   seq_num         NUMBER,
   msg_number      NUMBER,
   message         VARCHAR2(4000));

Using a VARRAY

You can save the output of CAN_SYNCREF_TABLE in a PL/SQL VARRAY. The elements of this array are of type CanSyncRefMessage, which is predefined in the SYS schema, as shown in the following:

TYPE CanSyncRefMessage IS OBJECT (
   schema_name     VARCHAR2(30),
   table_name      VARCHAR2(30),
   mv_schema_name  VARCHAR2(30),
   mv_name         VARCHAR2(30),
   eligible        VARCHAR2(1),
   seq_num         NUMBER,
   msg_number      NUMBER,
   message         VARCHAR2(4000));

The array type CanSyncRefArrayType, which is a varray of CanSyncRefMessage objects, is predefined in the SYS schema as follows:

TYPE CanSyncRefArrayType AS VARRAY(256) OF CanSyncRefMessage;

Each CanSyncRefMessage record provides a message concerning the eligibility of the base table or a dependent materialized view for synchronous refresh. The semantics of the fields is the same as that of the corresponding fields in the SYNCREF_TABLE. However, the SYNCREF_TABLE has a statement_id field which is absent in CanSyncRefMessage because no statement_id is supplied (because it is not required) when CAN_SYNCREF_TABLE is called with a VARRAY parameter.

Note this procedure will only perform the refresh on those materialized views that have been registered for synch refresh; any other materialized views will become stale once this procedure completes.

For more information on how to monitor the status of the two synchronous refresh operations, PREPARE_REFRESH and EXECUTE_REFRESH and how to troubleshoot errors that might occur using the information in the catalog views, refer to "Trouble-Shooting Synchronous Refresh Operations" in Oracle Database Data Warehousing Guide.

This procedure is overloaded.

Syntax

DBMS_SYNC_REFRESH.EXECUTE_REFRESH (
   group_id   IN NUMBER);

DBMS_SYNC_REFRESH.EXECUTE_REFRESH (
   group_id_list  IN DBMS_UTILITY.NUMBER_ARRAY);

Parameters

Usage Notes

This procedure also releases the locks placed on the tables in the sync refresh group that were placed on them by the PREPARE_REFRESH procedure. See "PREPARE_REFRESH Procedure" for a description of these locks and Oracle Database Reference for information regarding the status of the refresh operation after DBMS_SYNC_REFRESH.EXECUTE_REFRESH.

Syntax

FUNCTION DBMS_SYNC_REFRESH.GET_ALL_GROUP_IDS
           RETURN DBMS_UTILITY.NUMBER_ARRAY;

Parameters

Syntax

DBMS_SYNC_REFRESH.GET_GROUP_ID (
   object_name_list   IN VARCHAR2)
RETURN DBMS_UTILITY.NUMBER_ARRAY;

Parameters

Syntax

DBMS_SYNC_REFRESH.GET_GROUP_ID_LIST (
   object_name_list   IN VARCHAR2)
RETURN DBMS_UTILITY.NUMBER_ARRAY;

Parameters

A sync refresh group consists of a set of related tables and all materialized views dependent on those base tables. Note this procedure will only prepare for refresh those dependent materialized views that have been registered for synchronous refresh.

For more information on how to monitor the status of the two synchronous refresh operations, PREPARE_REFRESH and EXECUTE_REFRESH and how to troubleshoot errors that might occur using the information in the catalog views, refer to "Trouble-Shooting Synchronous Refresh Operations" in Oracle Database Data Warehousing Guide.

Syntax

DBMS_SYNC_REFRESH.PREPARE_REFRESH (
   group_id   IN NUMBER)
RETURN DBMS_UTILITY.NUMBER_ARRAY;

Parameters

Usage Notes

This procedure plans the three phases of the sync refresh operation and executes the steps associated with the prepare phase itself. These steps include identifying the partitions of the fact tables and materialized views that have been changed, and computing their new values as a result of the changes. The new values of the partitions are stored in tables called outside tables that are exchanged into their corresponding partitions at the time of the EXECUTE_REFRESH.

Before running this procedure, the user must run PREPARE_STAGING_LOG on all tables in the group. This is required even for staging logs that do not have changes in them. The user must also register any partition operations on the tables in the group using the REGISTER_PARTITION_OPERATION.

One of the side effects of this procedure is that the tables being prepared are locked in this sense: the staging logs of the tables will be locked to prevent any DMLs from occurring and the registration of partition operations will be disabled. These locks will be in effect until you issue an EXECUTE_REFRESH statement. Alternatively, you can issue an ABORT_REFRESH operation to release these locks. Another side effect of this procedure is that it purges from the catalog records of earlier sync refresh operations; if they are older than the retention period, they are purged.

The degree of parallelism of the prepare refresh job is inherited from the session parameters which you can control with an ALTER SESSION statement.

The group ID of a table can be found using GET_GROUP_ID(table_name). The group IDs of a list of tables can be found with GET_GROUP_ID_LIST(table_name_list). The group IDs of all the lists of tables can be retrieved with GET_ALL_GROUP_IDS.

By default, synchronous refresh does not maintain global indexes belonging to the tables and materialized views in the sync refresh group. If you wish to do so, you can set event 31904, level 64 before executing PREPARE_REFRESH. This will cause the partition exchange DDL statements generated by PREPARE_REFRESH to have the UPDATE INDEXES clause appended to them, and when they are executed by EXECUTE_REFRESH, the global indexes will be maintained.

It can be run in several different modes ranging from the enforced mode in which strict checking of the data is done to trusted mode in which no checking is done. You should run this procedure after loading the staging log and before running PREPARE_REFRESH.

In the enforced mode, which is the default, this procedure will fill in the missing values of the columns of the rows being deleted or updated. An error is thrown if any violations of the staging-log rules are found. You can query the view USER_SR_STLOG_EXCEPTIONS to get details on the exceptions.

The notion of the staging log key is described in Oracle Database Data Warehousing Guide.

In the enforced mode, this procedure processes each delete/update row in the staging log as follows:

• It verifies the existence of the row in the base table using the key.

• For the rows being deleted (DMLTYPE$$ is 'D'), it verifies a row with this key exists in the base table; if non-null non-key values are supplied in the staging log, it verifies the values match the corresponding columns in the base table; else an exception is logged in the exceptions table. If the values of any of the non-key columns are missing, it fills in those values from the row in the base table.

• For the rows being updated (DMLTYPE$$ is 'UO' or 'UN'), it verifies a row with this key exists in the base table. In the old values row (DMLTYPE$$ is 'UO'), it makes the same check and does the same processing as with rows being deleted. In the new values row (DMLTYPE$$ is 'UN'), it checks that at least the value of one the columns differs from its old value; else an exception is logged.

• In the new values row (DMLTYPE$$ is 'UN'), a null value in a column is interpreted as having the same value as the old value of the column except if the old value is non-null and the new value is null in which case, the new value of the column is interpreted as being null. This requires that the user must provide the old value of columns which are being updated to NULL.

In the default enforced mode, this procedure verifies that each key is specified for at most once for a delete or update operation. This means that the user, when doing the change consolidation, must consolidate delete-insert of the same row into an update operation with rows 'UO' and 'UN;' multiple updates must be consolidated into a single update; and null changes such as an insert-update-delete of the same row must not appear in the staging log.

The checking done in the enforced mode can be time-consuming. If you are confident in the integrity of the data, you can choose a lower level of checking. You can choose to:

• trust all the insert rows (DMLTYPE$$ is 'I') by choosing the psl_mode of DBMS_SYNC_REFRESH.INSERT_TRUSTED

• trust all the delete rows (DMLTYPE$$ is 'D') by choosing the psl_mode of DBMS_SYNC_REFRESH.DELETE_TRUSTED

• trust all the update rows (DMLTYPE$$ is 'UO' or 'UN') by choosing the psl_mode of DBMS_SYNC_REFRESH.UPDATE_TRUSTED

• trust all three types of DMLs by choosing the psl_mode of DBMS_SYNC_REFRESH.TRUSTED.

In addition, you can specify the psl_mode as a bitmask of the flags described above. For example, DBMS_SYNC_REFRESH.INSERT_TRUSTED + DBMS_SYNC_REFRESH_DELETE_TRUSTED will treat inserts and deletes to be trusted but not updates.

Syntax

DBMS_SYNC_REFRESH.PREPARE_STAGING_LOG (
   schema_name      IN VARCHAR2,
   base_table_name  IN VARCHAR2,
   psl_mode         IN NUMBER DEFAULT
   DBMS_SYNC_REFRESH.ENFORCED);

Parameters

This procedure requires the SYSDBA privilege in addition to the privilege to execute it.

Syntax

DBMS_SYNC_REFRESH.PURGE_REFRESH_STATS (
   before_timestamp IN TIMESTAMP WITH TIME ZONE);

Parameters

It checks each materialized view in the list for eligibility and places it in the sync refresh group it belongs to. A sync refresh group is a set of related tables and materialized views defined on top of them. Two tables are considered related if there is a referential constraint between them.

The eligibility rules of materialized views for synchronous refresh are described in detail in Oracle Database Data Warehousing Guide. The principal requirements are that the materialized view must be partitioned and its partition key must be derivable from the partition key of its fact table. The materialized view definition must specify the USING TRUSTED CONSTRAINTS clause because sync refresh trusts the foreign key and primary key relationships to perform various refresh optimizations. The materialized view's refresh policy must be specified as ON DEMAND.

You have an option to register only some of the materialized views associated with a table, and leave some unregistered. Oracle Corporation does not recommend this, and in such a case, the user has to maintain the unregistered ones using the PCT or complete refresh methods.

A staging table must have been created for each base table of each materialized view in the materialized view list (mv_list), or else an error is thrown.

If any of the materialized views are not eligible for sync refresh, an error is thrown and the registration of all materialized views in the materialized view list fails.

Syntax

DBMS_SYNC_REFRESH.REGISTER_MVIEWS (
   mv_list   IN VARCHAR2);

Parameter

Syntax

DBMS_SYNC_REFRESH.REGISTER_PARTITION_OPERATION (
   partition_op                IN VARCHAR2,
   schema_name                 IN VARCHAR2,
   base_table_name             IN VARCHAR2,
   partition_name              IN VARCHAR2,
   outside_partn_table_schema  IN VARCHAR2,
   outside_partn_table_name    IN VARCHAR2);

Parameters

Usage Notes

The three kinds of change operations that may be specified on partitions are DROP, TRUNCATE, and EXCHANGE.

If DROP is specified, then the partition will be dropped from the base table at the time of EXECUTE_REFRESH. If TRUNCATE is specified, then the data from the partition will be deleted but the partition itself will not be dropped. These operations provide a more efficient way of specifying the deletes of all the rows in a partition than specifying them individually in the staging log.

If EXCHANGE is specified, then the contents of the outside table is exchanged with contents of the specified partition of EXECUTE_REFRESH. This provides an alternative method to the user of providing the changes to the base tables instead of populating the staging log.

Syntax

DBMS_SYNC_REFRESH.UNREGISTER_MVIEWS (
   mv_list   IN VARCHAR20;

Parameter

Syntax

DBMS_SYNC_REFRESH.UNREGISTER_PARTITION_OPERATION (
   partition_op     IN VARCHAR2,
   schema_name      IN VARCHAR2,
   base_table_name  IN VARCHAR2,
   partition_name   IN VARCHAR2);

Parameters