Oracle | DBMS_SYNC

Oracle DBMS_SYNC_REFRESH Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Synchronous refresh is a refresh method introduced in Oracle Database Release 12c to enable synchronizing a set of tables and dependent materialized views.

Note Before running demos on this page create the SYNCREF_TABLE table in the schema in which you will be working by running the script {ORACLE_HOME}/rdbms/admin/utlsrt.sql from that schema.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
DELETE_TRUSTED	BINARY_INTEGER	2
ENFORCED	BINARY_INTEGER	0
INSERT_TRUSTED	BINARY_INTEGER	1
TRUSTED (trust all three DML types)	BINARY_INTEGER	7
UPDATE_TRUSTED	BINARY_INTEGER	4

Data Types -- defined in catsnap.sql CREATE OR REPLACE 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)); / CREATE OR REPLACE TYPE CanSyncRefArrayType AS VARRAY(256) OF Sys.CanSyncRefMessage; / GRANT EXECUTE ON SYS.CanSyncRefMessage TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM CanSyncRefMessage FOR SYS.CanSyncRefMessage; / GRANT EXECUTE ON SYS.CanSyncRefArrayType TO PUBLIC; / CREATE OR REPLACE PUBLIC SYNONYM CanSyncRefArrayType FOR SYS.CanSyncRefArrayType; /

Dependencies

CANSYNCREFARRAYTYPE	DBMS_SYNC_REFRESH_INTERNAL	DBMS_UTILITY
DBMS_SNAPSHOT_COMMON	DBMS_SYS_ERROR

Documented Yes

Exceptions

Error Code	Reason
ORA-31927	Staging log does not exist on table <table_name>
ORA-31928	Synchronous refresh error
QSM-03238	The materialized view was not defined on the base table <mv_table_name> for synchronous
QSM-03249	The table <table_name> does not have a staging log
QSM-03281	The specified GROUP_ID is not valid
QSM-03283	The materialized view <mv_name> has not been registered

First Available 12.1.0

Security Model Owned by SYS with EXECUTE granted to PUBLIC which seems like a really bad idea. Should anyone that can create a session be able to purge refresh stats or unregister a partition operation? We don't think so.

Source {ORACLE_HOME}/rdbms/admin/prvtsnap.plb

Subprograms

ABORT_REFRESH	GET_GROUP_ID	REGISTER_MVIEWS
ALTER_REFRESH_STATS_RETENTION	GET_GROUP_ID_LIST	REGISTER_PARTITION_OPERATION
CAN_SYNCREF_TABLE	PREPARE_REFRESH	UNREGISTER_MVIEWS
EXECUTE_REFRESH	PREPARE_STAGING_LOG	UNREGISTER_PARTITION_OPERATION
GET_ALL_GROUP_IDS	PURGE_REFRESH_STATS

ABORT_REFRESH

Aborts a refresh if a PREPARE_REFRESH or EXECUTE_REFRESH statement has failed.

Overload 1 dbms_sync_refresh.abort_refresh(group_id IN BINARY_INTEGER);

SELECT owner, group_id, status, prepare_start_time, execute_start_time

      FROM dba_sr_grp_status_all;

      

      exec dbms_sync_refresh.abort_refresh(42);

Overload 2 dbms_sync_refresh.abort_refresh(group_id_list IN dbms_utility.number_array);

DECLARE

       grp_ids dbms_utility.number_array;

      BEGIN

        grp_ids(1) := 42;

        grp_ids(2) := 44;

        grp_ids(3) := 45;

      

        dbms_sync_refresh.abort_refresh(grp_ids);

      END;

      /

ALTER_REFRESH_STATS_RETENTION

Changes refresh stats retention in days

dbms_sync_refresh.alter_refresh_stats_retention(retention IN NUMBER);

Value	Description
1 to 365,000	Valid range of days
0	Refresh history not saved by PREPARE_REFRESH
-1	Refresh history not purged by PREPARE_REFRESH
NULL	Return to default value (31)

col parameter_name format a35

      col str_value format a20

      

      SELECT *

      FROM syncref$_parameters;

      

      exec dbms_sync_refresh.alter_refresh_stats_retention(90);

      

      SELECT *

      FROM syncref$_parameters;

CAN_SYNCREF_TABLE

Advises whether a table and dependent MVs are eligible for synchronous refresh

Overload 1 dbms_sync_refresh.can_syncref_table( schema_name IN VARCHAR2, table_name IN VARCHAR2, statement_id IN VARCHAR2);

conn uwclass/uwclass@pdbdev

      

      CREATE MATERIALIZED VIEW mv_force

      TABLESPACE uwdata

      NOCACHE

      LOGGING

      NOCOMPRESS

      NOPARALLEL

      BUILD IMMEDIATE

      REFRESH FORCE ON DEMAND

      WITH ROWID AS

      SELECT * FROM servers;

      

      exec dbms_sync_refresh.can_syncref_table('UWCLASS', 'SERVERS', 'SRTest');

      

      col statement_id format a12

      col schema_name format a11

      col table_name format a10

      col mv_name format a12

      col eligible format a4

      col message format a45

      

      SELECT statement_id, schema_name, table_name, mv_name, eligible, message

      FROM syncref_table;

      

      TRUNCATE TABLE syncref_table;

      

      DROP MATERIALIZED VIEW mv_force;

      

      CREATE MATERIALIZED VIEW mv_force

      TABLESPACE uwdata

      NOCACHE

      LOGGING

      NOCOMPRESS

      NOPARALLEL

      BUILD IMMEDIATE

      REFRESH FORCE ON DEMAND

      USING TRUSTED CONSTRAINTS

      WITH ROWID AS

      SELECT * FROM servers;

      

      exec dbms_sync_refresh.can_syncref_table('UWCLASS', 'SERVERS', 'SRTest');

      

      SELECT statement_id, schema_name, table_name, mv_name, eligible, message

      FROM syncref_table;

      

      STATEMENT_ID SCHEMA_NAME TABLE_NAME MV_NAME      ELIG MESSAGE

      ------------ ----------- ---------- ------------ ---- ---------------------------------------------

      SRTest       UWCLASS     SERVERS    MV_FORCE     N    The table UWCLASS.SERVERS is classified as neither a fact nor a dimension table.

      SRTest       UWCLASS     SERVERS    MV_FORCE     N    The materialized view fails the join graph eligibility check for synchronous refresh.

Overload 2 dbms_sync_refresh.can_syncref_table( schema_name IN VARCHAR2, table_name IN VARCHAR2, output_array IN OUT sys.canSyncRefArrayType;

TBD

EXECUTE_REFRESH

Execute a synchronous refresh

Overload 1 dbms_sync_refresh.execute_refresh(group_id IN BINARY_INTEGER);

SELECT owner, group_id, status, prepare_start_time, execute_start_time

      FROM dba_sr_grp_status_all;

      

      exec dbms_sync_refresh.execute_refresh(42);

Overload 2 dbms_sync_refresh.execute_refresh(group_id_list IN dbms_utility.number_array);

TBD

GET_ALL_GROUP_IDS

Returns the Group IDs of all synchronous refresh groups dbms_sync_refresh.get_all_group_ids RETURN dbms_utility.number_array;

DECLARE

       grp_ids dbms_utility.number_array;

      BEGIN

        grp_ids :=  dbms_sync_refresh.get_all_group_ids;

        IF grp_ids.COUNT <> 0 THEN

          FOR i IN 1..grp_ids.COUNT LOOP

            dbms_output.put_line(grp_ids(i));

          END LOOP;

        ELSE

          dbms_output.put_line('No Group IDs Were Found');

        END IF;

      END;

      /

GET_GROUP_ID

Returns the Group ID of a named table or materialized view dbms_sync_refresh.get_group_id(object_name IN VARCHAR2) RETURN NUMBER;

SELECT dbms_sync_refresh.get_group_id('MV_FORCE')

      FROM dual;

GET_GROUP_ID_LIST

Returns the group IDs of the tables and materialized views in a list of objects dbms_sync_refresh.get_group_id_list(object_name_list IN VARCHAR2) RETURN dbms_utility.number_array;

DECLARE

       grp_ids dbms_utility.number_array;

      BEGIN

        grp_ids :=  dbms_sync_refresh.get_group_id_list('MV_FORCE');

        IF grp_ids.COUNT <> 0 THEN

          FOR i IN 1..grp_ids.COUNT LOOP

            dbms_output.put_line(grp_ids(i));

          END LOOP;

        ELSE

          dbms_output.put_line('No Group IDs Were Found For The Table');

        END IF;

      END;

      /

PREPARE_REFRESH

Prepares the sync refresh groups for refresh

Overload 1 dbms_sync_refresh.prepare_refresh(group_id IN BINARY_INTEGER);

SELECT owner, group_id, status, prepare_start_time, execute_start_time

      FROM dba_sr_grp_status_all;

      

      exec dbms_sync_refresh.prepare_refresh(42);

Overload 2 dbms_sync_refresh.prepare_refresh(group_id_list IN dbms_utility.number_array);

DECLARE

       grp_ids dbms_utility.number_array;

      BEGIN

        grp_ids(1) := 42;

        grp_ids(2) := 44;

        grp_ids(3) := 45;

      

        dbms_sync_refresh.prepare_refresh(grp_ids);

      END;

      /

PREPARE_STAGING_LOG

Validates and collects statistics on the data in the staging log. Should be run after loading the staging log and before running PREPARE_REFRESH. dbms_sync_refresh.prepare_staging_log( schema_name IN VARCHAR2, base_table_name IN VARCHAR2, psl_mode IN BINARY_INTEGER);

exec dbms_sync_refresh.prepare_staging_log('UWCLASS', 'MV_FORCE', dbms_sync_refresh.insert_trusted+dbms_sync_refresh.update_trusted);

      

      -- wants a staging log created first but no info as to how to do that.

      

      -- this view is documented but does not exist causing errors ... dropped in e1102000.sql

      SELECT *

      FROM dba_sr_log_exceptions;

PURGE_REFRESH_STATS

Purges the refresh history of sync refreshes dbms_sync_refresh.purge_refresh_stats(before_timestamp IN TIMESTAMP WITH TIME ZONE);

exec dbms_sync_refresh.purge_refresh_stats(SYSTIMESTAMP-90);

REGISTER_MVIEWS

Enables the administration of individual materialized views. It is invoked at a master site or master materialized view site to register a materialized. dbms_sync_refresh.register_mviews(mv_list IN VARCHAR2);

conn uwclass/uwclass@pdbdev

      

      CREATE MATERIALIZED VIEW mv_force

      TABLESPACE uwdata

      NOCACHE

      LOGGING

      NOCOMPRESS

      NOPARALLEL

      BUILD IMMEDIATE

      REFRESH FORCE ON DEMAND

      WITH ROWID AS

      SELECT * FROM servers;

      

      exec dbms_sync_refresh.register_mviews('MV_FORCE');

      

      SELECT *

      FROM dba_registered_mviews;

REGISTER_PARTITION_OPERATION

Registers a partition maintenance operation on a partition of a base table dbms_sync_refresh.register_partition_operation( partition_op IN VARCHAR2, -- DROP, EXCHANGE, or TRUNCATE 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, validation IN BOOLEAN);

TBD: Need to create a working demo

UNREGISTER_MVIEWS

Enables the administration of individual materialized views. It is invoked at a master site or master materialized view site to unregister a materialized view. dbms_sync_refresh.unregister_mviews(mv_list IN VARCHAR2);

exec dbms_sync_refresh.unregister_mviews('MV_FORCE');

UNREGISTER_PARTITION_OPERATION

Unregisters a partition maintenance operation dbms_sync_refresh.unregister_partition_operation( partition_op IN VARCHAR2, schema_name IN VARCHAR2, base_table_name IN VARCHAR2, partition_name IN VARCHAR2);

exec dbms_sync_refresh.unregister_partition_operation('TRUNCATE', 'UWCLASS', 'STUDENTS', 'P2020Q1');