哎

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;