Oracle DBMS_SYNC_REFRESH
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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');

Related Topics
Built-in Functions
Built-in Packages
DBMS_PDB
DBMS_MVIEW
DBMS_REFRESH
DBMS_SNAPSHOT
DBMS_SNAPSHOT_UTL
DBMS_SYNC_REFRESH_INTERNAL
Materialized Views
SYS_CONTEXT
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx