Oracle DBMS_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 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Management of materialized view (snapshot) refresh groups
AUTHID CURRENT_USER
Constants
Name Data Type Value
REPAPI_RGROUP NUMBER 8
Dependencies
DBA_REGISTERED_MVIEW_GROUPS DBMS_REPCAT_RGT_CUST2 KU$_REFGROUP_VIEW
DBMS_IJOB DBMS_REPCAT_SNA_UTL RGCHILD$
DBMS_IREFRESH DBMS_SYS_ERROR 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
DBMS_MVIEW
DBMS_SNAPSHOT_UTL
Materialized Views
Packages

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