Oracle | DBMS

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	MAKE_REPAPI	USER_EXPORT
CHANGE	REFRESH	USER_EXPORT_CHILD
DESTROY	SUBTRACT	Package Demo
MAKE

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;