Oracle DBMS_REFRESH
Version 21c

General Information
Library Note Morgan's Library Page Header
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 Management of materialized view (snapshot) refresh groups
AUTHID CURRENT_USER
Constants
Name Data Type Value
REPAPI_RGROUP NUMBER 8
Dependencies
CREATEDUPLICATEDTABLEREFRESHGROUP DBMS_IREFRESH DBMS_SYSTEM
DBA_SCHEDULER_JOBS DBMS_ISNAPSHOT DBMS_UTILITY
DBMS_ASSERT DBMS_SNAPSHOT_COMMON MODIFYDUPLICATEDTABLEREFRESHGROUP
DBMS_IJOB DBMS_STANDARD REMOVEDUPLICATEDTABLEREFRESHGROUP
DBMS_INTERNAL_LOGSTDBY    
Documented Yes: Packages and Types Reference
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 21c
What's New In 23c

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