Oracle | DBMS

Oracle DBMS_REFRESH Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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)

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

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;