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.
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;
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;
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;
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;
/
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;
/
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;
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);
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);
dbms_sync_refresh.unregister_partition_operation(
partition_op IN VARCHAR2,
schema_name IN VARCHAR2,
base_table_name IN VARCHAR2,
partition_name IN VARCHAR2);