Thursday, 23 November 2017

DBMS_REFRESH DBMS_SYNC_REFRESH

DBMS_REFRESH

The DBMS_REFRESH package enables you to create groups of materialized views that can be refreshed together to a transactionally consistent point in time. These groups are called refresh groups.
Users must have the EXECUTE privilege to run the procedures of DBMS_REFRESH package.
 

ADD Procedure

This procedure adds materialized views to a refresh group.
Syntax
DBMS_REFRESH.ADD (
   name     IN VARCHAR2,
   { list   IN VARCHAR2, 
   | tab    IN DBMS_UTILITY.UNCL_ARRAY, }
   lax      IN BOOLEAN := FALSE);
 
list
Comma-delimited list of materialized views that you want to add to the refresh group. Synonyms are not supported.
 
 

CHANGE Procedure

This procedure changes the refresh interval for a refresh group.
Syntax
DBMS_REFRESH.CHANGE (
   name                  IN VARCHAR2,
   next_date             IN DATE           := NULL,
   interval              IN VARCHAR2       := NULL,
   implicit_destroy      IN BOOLEAN        := NULL,
   rollback_seg          IN VARCHAR2       := NULL,
   push_deferred_rpc     IN BOOLEAN        := NULL,
   refresh_after_errors  IN BOOLEAN        := NULL,
   purge_option          IN BINARY_INTEGER := NULL,
   parallelism           IN BINARY_INTEGER := NULL,
   heap_size             IN BINARY_INTEGER := NULL);
 

DESTROY Procedure

This procedure removes all of the materialized views from a refresh group and delete the refresh group.
Syntax
DBMS_REFRESH.DESTROY (
   name   IN   VARCHAR2);
 

MAKE Procedure

This procedure specifies the members of a refresh group and the time interval used to determine when to refresh the members of this group.
Syntax
DBMS_REFRESH.MAKE (
   name                  IN    VARCHAR2
   { list                IN    VARCHAR2,
   | tab                 IN    DBMS_UTILITY.UNCL_ARRAY,}
   next_date             IN    DATE,
   interval              IN    VARCHAR2,
   implicit_destroy      IN    BOOLEAN         := FALSE,
   lax                   IN    BOOLEAN         := FALSE,
   job                   IN    BINARY_INTEGER  := 0,
   rollback_seg          IN    VARCHAR2        := NULL,
   push_deferred_rpc     IN    BOOLEAN         := TRUE,
   refresh_after_errors  IN    BOOLEAN         := FALSE
   purge_option          IN    BINARY_INTEGER  := NULL,
   parallelism           IN    BINARY_INTEGER  := NULL,
   heap_size             IN    BINARY_INTEGER  := NULL);
 
 

REFRESH Procedure

This procedure manually refreshes a refresh group.
Syntax
DBMS_REFRESH.REFRESH (
   name   IN    VARCHAR2);
 

SUBTRACT Procedure

This procedure removes materialized views from a refresh group.
Syntax
DBMS_REFRESH.SUBTRACT (
   name      IN    VARCHAR2,
   { list    IN    VARCHAR2,
   | tab     IN    DBMS_UTILITY.UNCL_ARRAY, }
   lax       IN    BOOLEAN := FALSE);
 

DBMS_SYNC_REFRESH

The DBMS_SYNC_REFRESH package provides an interface to perform a synchronous refresh of materialized views
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.
 

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.
Syntax
DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id      IN NUMBER);
 
DBMS_SYNC_REFRESH.ABORT_REFRESH (
   group_id_list IN DBMS_UTILITY.NUMBER_ARRAY);
 

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

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.
Syntax
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.
Syntax
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
Syntax
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).
Syntax
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
Syntax
DBMS_SYNC_REFRESH.PREPARE_REFRESH (
   group_id   IN NUMBER)
RETURN DBMS_UTILITY.NUMBER_ARRAY;
 
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
 

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

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

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.
Syntax
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.
Syntax
DBMS_SYNC_REFRESH.UNREGISTER_PARTITION_OPERATION (
   partition_op     IN VARCHAR2,
   schema_name      IN VARCHAR2,
   base_table_name  IN VARCHAR2,

   partition_name   IN VARCHAR2);
at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)