Oracle DBMS_REFRESH
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Management of materialized view (snapshot) refresh groups
AUTHID CURRENT_USER
Constants
Name Data Type Value
REPAPI_RGROUP NUMBER 8
Dependencies (Updated to 12.2.0.1)
DBMS_IJOB DBMS_SNAPSHOT_COMMON RGCHILD$
DBMS_IREFRESH DBMS_STANDARD RGROUP$
DBMS_ISNAPSHOT DBMS_UTILITY  
Documented Yes
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmssnap.sql
Subprograms
 
ADD
Add A Refresh Group

Overload 1
dbms_refresh.add(
name      IN VARCHAR2,
list      IN VARCHAR2,
lax       IN BOOLEAN        := FALSE
siteid    IN BINARY_INTEGER := 0,
export_db IN VARCHAR2       := NULL );
See DBMS_REFRESH demo below
Overload 2 dbms_refresh.add(
name      IN VARCHAR2,
tab       IN DBMS_UTILITY.UNCL_ARRAY,
lax       IN BOOLEAN        := FALSE
siteid    IN BINARY_INTEGER := 0,
export_db IN VARCHAR2       := NULL );
See DBMS_REFRESH demo below
 
CHANGE
Change A Refresh Group dbms_refresh.add(
name                 IN VARCHAR2,
next_date            IN DATE           := NULL,
interval             IN VARCHAR2       := NULL,
implicit_destroy     IN BOOLEAN        := NULL,
rollback_seg         IN VARCHAR2       := NULL,
push_deferred_rpc    IN BOOLEAN        := NULL,
refresh_after_errors IN BOOLEAN        := NULL,
purge_option         IN BINARY_INTEGER := NULL,
parallelism          IN BINARY_INTEGER := NULL,
heap_size            IN BINARY_INTEGER := NULL);
See DBMS_REFRESH demo below
 
DESTROY
Drop A Refresh Group dbms_refresh.destroy(name IN VARCHAR2);
See DBMS_REFRESH demo below
 
MAKE
Create A New Refresh Group

Overload 1
dbms_refresh.make(
name                 IN VARCHAR2
list                 IN VARCHAR2,
next_date            IN DATE,
interval             IN VARCHAR2,
implicit_destroy     IN BOOLEAN        := FALSE,
lax                  IN BOOLEAN        := FALSE,
job                  IN BINARY_INTEGER := 0,
rollback_seg         IN VARCHAR2       := NULL,
push_deferred_rpc    IN BOOLEAN        := TRUE,
refresh_after_errors IN BOOLEAN        := FALSE
purge_option         IN BINARY_INTEGER := 1,
parallelism          IN BINARY_INTEGER := 0,
heap_size            IN BINARY_INTEGER := 0);
exec dbms_refresh.make('group1', 'RB_ATT_DNS_LOOKUPS, RB_COUNTRIES, RB_GEOCODES', SYSDATE + 6, 'next_day(trunc(sysdate), ''SUNDAY'') + 3/24', FALSE, TRUE);
Overload 2 dbms_refresh.make(
name                 IN VARCHAR2
tab                  IN DBMS_UTILITY.UNCL_ARRAY,
next_date            IN DATE,
interval             IN VARCHAR2,
implicit_destroy     IN BOOLEAN        := FALSE,
lax                  IN BOOLEAN        := FALSE,
job                  IN BINARY_INTEGER := 0,
rollback_seg         IN VARCHAR2       := NULL,
push_deferred_rpc    IN BOOLEAN        := TRUE,
refresh_after_errors IN BOOLEAN        := FALSE
purge_option         IN BINARY_INTEGER := 1,
parallelism          IN BINARY_INTEGER := 0,
heap_size            IN BINARY_INTEGER := 0);
See DBMS_REFRESH demo below
 
MAKE_REPAPI
Undocumented dbms_refresh.make_repapi(
refgroup    IN BINARY_INTEGER,
name        IN VARCHAR2,
siteid      IN BINARY_INTEGER,
refresh_seq IN BINARY_INTEGER,
export_db   IN VARCHAR2,
flag        IN BINARY_INTEGER DEFAULT REPAPI_RGROUP);
TBD
 
REFRESH
Manually refreshes a refresh group dbms_refresh.refresh(name IN VARCHAR2);
See DBMS_REFRESH demo below
 
SUBTRACT
Removes materialized views from a refresh group

Overload 1
dbms_refresh.subtract(
name  IN VARCHAR2,
list  IN VARCHAR2,
lax   IN BOOLEAN := FALSE);
See DBMS_REFRESH demo below
Overload 2 dbms_refresh.subtract(
name IN VARCHAR2,
tab  IN dbms_utility.uncl_array,
lax  IN BOOLEAN := FALSE);
See DBMS_REFRESH demo below
 
USER_EXPORT
Produces the text of a call for recreating the given group dbms_refresh.user_export(
rg#    IN     BINARY_INTEGER,
mycall IN OUT VARCHAR2);
TBD
 
USER_EXPORT_CHILD
Produces the text of a call for recreating the given group item dbms_refresh.user_export_child(
myowner  IN     VARCHAR2,
myname   IN     VARCHAR2,
mytype   IN     VARCHAR2,
mycall   IN OUT VARCHAR2,
mysite   IN     BINARY_INTEGER := 0);
TBD
 
Refresh Group Demo
Demo using DBMS_REFRESH package components conn sys@pdbdev as sysdba

GRANT select ON ku$_refgroup_view TO uwclass;

conn uwclass/uwclass@pdbdev

col refname format a12
col refowner format a12
col ref_child format a80

desc sys.ku$_refgroup_view

SELECT COUNT(*) FROM sys.ku$_refgroup_view;

CREATE MATERIALIZED VIEW mv1
TABLESPACE uwdata
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

CREATE MATERIALIZED VIEW mv2
TABLESPACE uwdata
REFRESH COMPLETE
AS SELECT s.srvr_id, AVG(latitude)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

CREATE MATERIALIZED VIEW mv3
TABLESPACE uwdata
REFRESH COMPLETE
AS SELECT s.srvr_id, AVG(longitude)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

exec dbms_refresh.make('REFGRP','mv1,mv2' ,SYSDATE+1/96,'SYSDATE+1/96',FALSE,TRUE);

col rowner format a20
col rname format a20
col interval format a20

SELECT rowner, rname, refgroup, next_date, interval
FROM dba_refresh;

SELECT refname, refowner, ref_make_user
FROM sys.ku$_refgroup_view;

SELECT refname, refowner, ref_make_dba
FROM sys.ku$_refgroup_view;

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

desc dba_refresh_children

SELECT owner, name, type, rname
FROM dba_refresh_children;

exec dbms_refresh.add('REFGRP', 'mv3');

SELECT owner, name, type, rname, parallelism
FROM dba_refresh_children;

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

exec dbms_refresh.change(name=>'REFGRP', parallelism=>2);

SELECT owner, name, type, rname, parallelism
FROM dba_refresh_children;

UPDATE serv_inst
SET srvr_id = 14;

SELECT * FROM mv1;
SELECT * FROM mv2;
SELECT * FROM mv3;

COMMIT;

SELECT * FROM mv1;
SELECT * FROM mv2;
SELECT * FROM mv3;

-- wait 10+ minutes or force the refresh manually using the following:
exec dbms_refresh.refresh('REFGRP');

SELECT * FROM mv1;
SELECT * FROM mv2;
SELECT * FROM mv3;

exec dbms_refresh.subtract('REFGRP', 'mv2');

SELECT owner, name, type, rname, parallelism
FROM dba_refresh_children;

exec dbms_refresh.destroy('REFGRP');

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

Related Topics
Built-in Functions
Built-in Packages
DBMS_IREFRESH
DBMS_MVIEW
DBMS_REFRESH
DBMS_SNAPSHOT_UTL
Materialized Views
What's New In 12cR1
What's New In 12cR2

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