Oracle DBMS_REFRESH


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

Search the Reference Library pages:

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_REFRESH

Version 11.1

General

Note: DBMS_REFRESH is used to manage materialized view (snapshot) refresh groups

Source

{ORACLE_HOME}/rdbms/admin/dbmssnap.sql

First Available

7.3.4

Constants

Name	Data Type	Value
REPAPI_RGROUP	NUMBER	8

Dependencies

ku$_refgroup_view	rgroup$
DBA_REGISTERED_MVIEW_GROUPS	DBMS_REPCAT_RGT_CUST2
DBMS_IAS_MT_INST	DBMS_REPCAT_SNA_UTL
DBMS_IJOB	DBMS_SYS_ERROR
DBMS_IREFRESH	DBMS_UTILITY
DBMS_ISNAPSHOT	RGCHILD$
DBMS_REFRESH	RGROUP$

Security Model

Execute granted to PUBLIC

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

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

CHANGE

Change A Refresh Group

dbms_refresh.change(
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

DESTROY

Drop A Refresh Group

dbms_refresh.destroy(name IN VARCHAR2);

See DBMS_REFRESH demo

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

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

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

Overload 2

dbms_refresh.subtract(
name IN VARCHAR2,
tab IN DBMS_UTILITY.UNCL_ARRAY,
lax IN BOOLEAN := FALSE);

See DBMS_REFRESH demo

USER_EXPORT

Undocumented

dbms_refresh.user_export(
rg# IN BINARY_INTEGER,
mycall IN OUT VARCHAR2);

TBD

USER_EXPORT_CHILD

Undocumented

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 / as sysdba

GRANT select ON ku$_refgroup_view TO uwclass;

conn uwclass/uwclass

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

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;

exec dbms_refresh.add('REFGRP', 'mv3');

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

exec dbms_refresh.change(name=>'REFGRP', parallelism=>2);

SELECT refname, refowner, ref_make_dba
FROM sys.ku$_refgroup_view;

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

SELECT * FROM mv1;
SELECT * FROM mv2;
SELECT * FROM mv3;

exec dbms_refresh.subtract('REFGRP', 'mv2');

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

exec dbms_refresh.destroy('REFGRP');

SELECT refname, refowner, ref_child
FROM sys.ku$_refgroup_view;

Related Topics

DBMS_MVIEW

Materialized Views

Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us