CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 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 [62 users online]    © 2010 psoug.org