Oracle DBMS_SYNC_REFRESH
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 DEFINER
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
ALL_OBJECTS DBMS_ASSERT SYNCREF$_OBJECTS
ALL_PART_KEY_COLUMNS DBMS_IREFRESH SYNCREF$_OBJECT_STATUS
ALL_PART_TABLES DBMS_ISNAPSHOT SYNCREF$_PARAMETERS
ALL_TAB_PARTITIONS DBMS_ISYNCREF SYNCREF$_PARTN_OPS
CANSYNCREFARRAYTYPE DBMS_SNAPSHOT_LIB SYNCREF_TABLE
DBA_OBJECTS DBMS_STANDARD USER$
DBA_SR_GRP_STATUS DBMS_SYS_ERROR USER_SR_GROUP_STATUS
DBA_SR_GRP_STATUS_ALL DBMS_UTILITY USER_SR_OBJ
DBA_SR_OBJ DUAL USER_SR_OBJ_ALL
DBA_SR_OBJ_ALL OBJ$ USER_SR_OBJ_STATUS_ALL
DBA_SR_OBJ_STATUS PLITBLM USER_SR_OBJ_STATUS
DBA_SR_OBJ_STATUS_ALL SUMDETAIL$ USER_SR_STLOG_EXCEPTIONS
DBA_SR_STLOG_EXCEPTIONS SYNCREF$_GROUP_STATUS USER_SR_STLOG_STATS
DBA_SR_STLOG_STATS    
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 since it runs as SYS: It should definitely be more restricted).
Source {ORACLE_HOME}/rdbms/admin/prvtsnap.plb
Subprograms
 
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);
-- need to create the table and register it here

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

Related Topics
DBMS_PDB
DBMS_MVIEW
DBMS_REFRESH
DBMS_SNAPSHOT
DBMS_SNAPSHOT_UTL
Materialized Views
Packages
SYS_CONTEXT

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-2014 Daniel A. Morgan All Rights Reserved