Oracle 12cR2 DBMS

Oracle DBMS_REFRESH Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.

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