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