Oracle 12c DBMS_REFRESH Demos

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 11.2.0.3 to 12.1.0.1. 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.

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