165 DBMS_SYNC_REFRESH

Overview

Synchronous refresh is a refresh method introduced in Oracle Database Release 12c, which enables you to keep a set of tables and the materialized views defined on them to be always in sync.

Security Model

The execute privilege for this package is granted to PUBLIC, so all users can execute the procedures in this package to perform synchronous refresh on objects owned by them. The database administrator can perform synchronous refresh operations on all tables and materialized views in the database.

In general, if a user without the DBA privilege wants to use synchronous refresh on another user's table, he must complete privileges to read from and write to that table, that it, the user must have the SELECT or READ, INSERT, UPDATE, and DELETE privileges on that table or materialized view. A couple of exceptions are:

• PURGE_REFRESH_STATS and ALTER_REFRESH_STATS_RETENTION Functions

  These two functions implement the purge policy and can be used to change the default retention period. These functions can be only be executed by the database administrator.

• The CAN_SYNCREF_TABLE Function

  This is an advisory function which examines the eligibility for sync refresh of all the materialized views associated with a specified table. Hence, this function requires the SELECT or READ privilege on all materialized views associated with the specified table.

ABORT_REFRESH Procedure

This procedure undoes all the changes made by PREPARE_REFRESH or EXECUTE_REFRESH for the specified sync refresh groups. It helps you to recover to a state where the tables and materialized views are usable and consistent in case they encounter unexpected errors.

This procedure is overloaded.

DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id      IN NUMBER);

DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id_list IN DBMS_UTILITY.NUMBER_ARRAY);

ALTER_REFRESH_STATS_RETENTION Procedure

This procedure alters the refresh history retention value, specified in days. It is intended for use in conjunction with PURGE_REFRESH_HISTORY. It also requires the SYSDBA privilege in addition to the privilege to execute it.

DBMS_SYNC_REFRESH.ALTER_REFRESH_STATS_RETENTION (
   retention   IN NUMBER);

CAN_SYNCREF_TABLE Procedure

This procedure advises on whether a table and its dependent materialized views are eligible for sync refresh. It provides an explanation of its analysis. If not eligible, you can examine the reasons and take appropriate action if possible.

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.

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);

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));

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));

TYPE CanSyncRefArrayType AS VARRAY(256) OF CanSyncRefMessage;

EXECUTE_REFRESH Procedure

This procedure executes sync refresh on the sync refresh groups prepared by DBMS_SYNC_REFRESH.PREPARE_REFRESH. These groups are identified by their group IDs. 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.

DBMS_SYNC_REFRESH.EXECUTE_REFRESH (
   group_id   IN NUMBER);

DBMS_SYNC_REFRESH.EXECUTE_REFRESH (
   group_id_list  IN DBMS_UTILITY.NUMBER_ARRAY);

GET_ALL_GROUP_IDS Function

This function returns the group IDs of all the sync refresh groups in the database.

FUNCTION DBMS_SYNC_REFRESH.GET_ALL_GROUP_IDS
           RETURN DBMS_UTILITY.NUMBER_ARRAY;

GET_GROUP_ID Function

This function returns the group ID of a materialized view. The group ID identifies the sync refresh group the table belongs to. A sync refresh group is a group of related tables and their dependent materialized views which must be all refreshed together jointly to ensure consistency and correctness.

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

GET_GROUP_ID_LIST Function

This function returns the group IDs of the tables in a given list of objects (materialized views).

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

PREPARE_REFRESH Procedure

This procedure prepares for refresh the sync refresh groups identified by the group ID in the input. 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.

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

PREPARE_STAGING_LOG Procedure

This procedure collects statistics on the data in the staging log of the base table and validates the data in the log. 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.

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

PURGE_REFRESH_STATS Procedure

This procedure purges the refresh history of sync refreshes that took place before the value specified by the BEFORE_TIMESTAMP parameter. This procedure requires the SYSDBA privilege in addition to the privilege to execute it.

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

REGISTER_MVIEWS

This procedure registers a list of materialized views for synchronous refresh. 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.

DBMS_SYNC_REFRESH.REGISTER_MVIEWS (
   mv_list   IN VARCHAR2);

REGISTER_PARTITION_OPERATION Procedure

This procedure registers a partition-maintenance operation (PMOP) on a partition of a base table.

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);

UNREGISTER_MVIEWS

This procedure unregisters a list of materialized views from synchronous refresh. Once a materialized view is unregistered, it can be maintained by the user with any of the traditional refresh methods, such as complete or PCT, refresh.

DBMS_SYNC_REFRESH.UNREGISTER_MVIEWS (
   mv_list   IN VARCHAR20;

UNREGISTER_PARTITION_OPERATION Procedure

This procedure unregisters a partition-maintenance operation (PMOP) that had been previously registered with REGISTER_PARTITION_OPERATION on a base table. The three kinds of change operations that can be specified on partitions are DROP, TRUNCATE, and EXCHANGE.

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