----SYNTAX
For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.
-----Parameter
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
# Referesh
exec dbms_mview.refresh('abc_mv'); //Mannual complete refresh
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F'); //Mannual fast (incremental) refresh (F denotes Fast)
Execute DBMS_MVIEW.REFRESH_DEPENDENT // Refreshes all table-based Oracle materialized views.
-----Test Demontration Non wirtable
create table abc as select * from scott.dept;
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
-create materialized view abc_mv as select * from abc; //Error because create materialize view to need base table one primary Key.
-create materialized view abc_mv as select * from abc_prim; //ONLY View not a writable
SQL> insert into dept_mv values(50,'TESTING','MUNICH'); insert into dept_mv values(50,'TESTING','MUNICH')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
drop materialized view abc_mv;
--------Test Demontration Updateable MVIEW (Not Refresh Perform DML)
Advantages:
Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication.
Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view abc_mv FOR UPDATE as select * from abc_prim;
select * from abc_prim;
select * from abc_mv;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from abc_mv;
select * from abc_prim
drop table abc_prim;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
drop materialized view abc_mv;
----Automatic fast refresh of materialized views (ON COMMIT REFERESH) (with refresh view log)
1.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view log on abc_prim;
create materialized view abc_mv REFRESH FAST ON COMMIT DISABLE QUERY REWRITE
as select * from scott.abc_prim;
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
insert into abc_prim values(50,'TEST','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
commit;
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
2.
CREATE MATERIALIZED VIEW abc_mv1
ENABLE QUERY REWRITE
AS SELECT *
FROM abc_prim
GROUP BY deptno
PCTFREE 5
PCTUSED 60
NOLOGGING PARALLEL 5
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/12;
# Purge MV log
-select * from MLOG$_abc_prim;
-insert into abc_prim values(50,'TEST','MUNICH');
-select * from MLOG$_abc_prim;
-execute DBMS_MVIEW.PURGE_LOG( master => 'T', num => 9999, flag => 'delete' ) ;
-select * from MLOG$_abc_prim;
----------REFRESH GROUPS - CLUBBING RELATED VIEWS
Metalink: http://www.databasejournal.com/features/oracle/article.php/10893_2200191_2/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm
Metalink: http://www.sqlsnippets.com/en/topic-12880.html
MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group
DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');
my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');
------Monitoring materialized views
select
substr(job,1,4) "Job", substr(log_user,1,5) "user",
substr(schema_user,1,5) "schema",
substr(to_char(last_date,'DD-MM-YYYY HH24:MI'),1,16) "last_date",
substr(to_char(next_date,'DD-MM-YYYY HH24:MI'),1,16) "next_date",
substr(broken,1,2) "B" ,substr(failures,1,6) "failed",
substr(what,1,20) "command"
From dba_jobs;
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;
Table 1 Refresh Modes
|
Refresh Mode
|
Description
|
|
Refresh occurs automatically when a transaction that modified
one of the materialized view's detail tables commits. This can be specified
as long as the materialized view is fast refreshable (in other words, not
complex). The ON COMMIT privilege is necessary to use this mode
|
|
|
Refresh occurs when a user manually executes one of the
available refresh procedures contained in the DBMS_MVIEW package
(REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)
|
Table 2 Refresh Options
|
Refresh Option
|
Description
|
|
Refreshes by recalculating the materialized view's defining
query.
|
|
|
Applies incremental changes to refresh the materialized view
using the information logged in the materialized view logs, or from a
SQL*Loader direct-path or a partition maintenance operation.
|
|
|
Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
|
|
|
Indicates that the materialized view will not be refreshed with
refresh mechanisms.
|
For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.
Both
values are in seconds.
SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='SCOTT';
-----Parameter
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced
# Referesh
exec dbms_mview.refresh('abc_mv'); //Mannual complete refresh
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F'); //Mannual fast (incremental) refresh (F denotes Fast)
Execute DBMS_MVIEW.REFRESH_DEPENDENT // Refreshes all table-based Oracle materialized views.
-----Test Demontration Non wirtable
create table abc as select * from scott.dept;
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
-create materialized view abc_mv as select * from abc; //Error because create materialize view to need base table one primary Key.
-create materialized view abc_mv as select * from abc_prim; //ONLY View not a writable
SQL> insert into dept_mv values(50,'TESTING','MUNICH'); insert into dept_mv values(50,'TESTING','MUNICH')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
drop materialized view abc_mv;
--------Test Demontration Updateable MVIEW (Not Refresh Perform DML)
Advantages:
Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication.
Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view abc_mv FOR UPDATE as select * from abc_prim;
select * from abc_prim;
select * from abc_mv;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from abc_mv;
select * from abc_prim
drop table abc_prim;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
drop materialized view abc_mv;
----Automatic fast refresh of materialized views (ON COMMIT REFERESH) (with refresh view log)
1.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view log on abc_prim;
create materialized view abc_mv REFRESH FAST ON COMMIT DISABLE QUERY REWRITE
as select * from scott.abc_prim;
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
insert into abc_prim values(50,'TEST','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
commit;
select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;
2.
CREATE MATERIALIZED VIEW abc_mv1
ENABLE QUERY REWRITE
AS SELECT *
FROM abc_prim
GROUP BY deptno
PCTFREE 5
PCTUSED 60
NOLOGGING PARALLEL 5
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/12;
# Purge MV log
-select * from MLOG$_abc_prim;
-insert into abc_prim values(50,'TEST','MUNICH');
-select * from MLOG$_abc_prim;
-execute DBMS_MVIEW.PURGE_LOG( master => 'T', num => 9999, flag => 'delete' ) ;
-select * from MLOG$_abc_prim;
----------REFRESH GROUPS - CLUBBING RELATED VIEWS
Metalink: http://www.databasejournal.com/features/oracle/article.php/10893_2200191_2/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm
Metalink: http://www.sqlsnippets.com/en/topic-12880.html
MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group
DBMS_REFRESH - Procedure MAKE
The MAKE procedure is used to create a new Refresh group.
We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');
my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD
Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');
------Monitoring materialized views
select
substr(job,1,4) "Job", substr(log_user,1,5) "user",
substr(schema_user,1,5) "schema",
substr(to_char(last_date,'DD-MM-YYYY HH24:MI'),1,16) "last_date",
substr(to_char(next_date,'DD-MM-YYYY HH24:MI'),1,16) "next_date",
substr(broken,1,2) "B" ,substr(failures,1,6) "failed",
substr(what,1,20) "command"
From dba_jobs;
-------Refresh Materialized Views
If a materialized view is configured to refresh on
commit, you should never need to manually refresh it, unless a rebuild is
necessary. Remember, refreshing on commit is a very intensive operation for
volatile base tables. It makes sense to use fast refreshes where possible.
For on demand refreshes, you can choose to manually
refresh the materialized view or refresh it as part of a refresh group.
The following code creates a refresh group defined to
refresh every minute and assigns a materialized view to it.
BEGIN
DBMS_REFRESH.make(
name => 'SCOTT.MINUTE_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ SYSDATE +
1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors
=> TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
BEGIN
DBMS_REFRESH.add(
name =>
'SCOTT.MINUTE_REFRESH',
list =>
'SCOTT.EMP_MV',
lax => TRUE);
END;
/
A materialized view can be manually refreshed using the DBMS_MVIEW package.
EXEC DBMS_MVIEW.refresh('EMP_MV');
Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called
using the ORACLE SCHEDULER
