| General Information |
| 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 |
| DBA_REGISTERED_MVIEW_GROUPS |
DBMS_REPCAT_SNA_UTL |
| DBMS_IJOB |
DBMS_SYS_ERROR |
| DBMS_IREFRESH |
DBMS_UTILITY |
| DBMS_ISNAPSHOT |
KU$_REFGROUP_VIEW |
| DBMS_REFRESH |
RGCHILD$ |
| DBMS_REPCAT_RGT_CUST2 |
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.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 |
| |
| 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 |
| 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 / as sysdba
GRANT select ON ku$_refgroup_view TO uwclass;
conn uwclass/uwclass
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; |
|
