Oracle Materialized view

1.
CREATE DATABASE LINK remotedb
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';

2.
CREATE MATERIALIZED VIEW items_summary_mv
ON PREBUILT TABLE
REFRESH FORCE AS
SELECT a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID,
sum(a.GMS) GMS,
sum(a.NET_REV) NET_REV,
sum(a.BOLD_FEE) BOLD_FEE,
sum(a.BIN_PRICE) BIN_PRICE,
sum(a.GLRY_FEE) GLRY_FEE,
sum(a.QTY_SOLD) QTY_SOLD,
count(a.ITEM_ID) UNITS
FROM items@remotedb a
GROUP BY a.PRD_ID, a.SITE_ID, a.TYPE_CODE, a.CATEG_ID;

3.
CREATE MATERIALIZED VIEW LOG ON items;

4.
exec DBMS_REFRESH.MAKE(name=>'my_grp', -
list=>'my_mv1,my_mv2', -
next_date => sysdate, -
interval => 'null');

5.
exec DBMS_REFRESH.REFRESH('my_grp');

6.
select * from all_refresh;
select * from all_refresh_children;
select * from sys.v_$mvrefresh;

//////////////////////////////////////////////////////////////////////////

1.
SQL> CREATE TABLE tab1 AS SELECT * FROM dict;

Table created.

2.
SQL> CREATE MATERIALIZED VIEW LOG ON tab1;
create materialized view log on tab1
*
ERROR at line 1:
ORA-12014: table ‘TAB1′ does not contain a primary key constraint


3.
SQL> desc tab1;
Name Null? Type
—————————————– ——– —————————-
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)

4.
SQL> ALTER TABLE tab1 ADD CONSTRAINT pk_tab1 PRIMARY KEY (table_name);

Table altered.

5.
SQL> CREATE MATERIALIZED VIEW LOG ON tab1;

Materialized view log created.

6.
SQL> connect system/*****
Connected.

7.
SQL> CREATE USER otherdb IDENTIFIED BY ***** DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

8.
SQL> GRANT connect, resource, create materialized view, create database link TO otherdb;

Grant succeeded.

9.
SQL> connect otherdb/*****
Connected.

10.
SQL> CREATE DATABASE LINK orcl.kt.lunet.ch@orcl CONNECT TO hpfuchs IDENTIFIED BY ***** USING ‘orcl’;

Database link created.


11.
SQL> CREATE MATERIALIZED VIEW mv1 REFRESH FAST AS SELECT * FROM tab1@orcl.kt.lunet.ch@orcl;

Materialized view created.

12.
SQL> CREATE INDEX ix_mv1_objecttype ON mv1 (table_name);

Index created.

13.
SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘MV1′,
next_date => sysdate,
interval => ’sysdate + 1′,
implicit_destroy => true,
lax => true);
END;
/

PL/SQL procedure successfully completed.

14.
SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);

PL/SQL procedure successfully completed.















SELECT mview_name, refresh_mode, refresh_method,
last_refresh_type, last_refresh_date
FROM user_mviews;


SELECT log_owner, master, log_table
FROM dba_mview_logs;


SELECT capability_name,
possible,
substr(msgtxt,1,60) AS msgtxt
FROM scott.mv_capabilities_table
WHERE capability_name like '%FAST%';



DROP MATERIALIZED VIEW scott.emp_v_MV;

CREATE MATERIALIZED VIEW scott.emp_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from emp_v
/
SQL> truncate table mv_capabilities_table;
SQL> exec dbms_mview.explain_mview('scott.emp_v_mv');

SQL> set linesize 100
SQL> SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt
FROM mv_capabilities_table
WHERE capability_name like '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - -------------
REFRESH_FAST N
REFRESH_FAST_AFTER_INSERT N named view in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT N named view in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT N view or subquery in from list
REFRESH_FAST_AFTER_INSERT N the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater

SQL> DROP MATERIALIZED VIEW scott.emp_v_MV;

SQL> CREATE MATERIALIZED VIEW scott.emp_v_MV
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
select * from emp;

SQL> TRUNCATE TABLE mv_capabilities_table;
SQL> EXEC dbms_mview.explain_mview('scott.emp_v_mv');

SQL> SELECT capability_name, possible, SUBSTR(msgtxt,1,60) AS msgtxt
FROM mv_capabilities_table
WHERE capability_name like '%FAST%';

CAPABILITY_NAME P MSGTXT
------------------------------ - ------------------------------------
REFRESH_FAST Y
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of the detail tables in the mater



SQL> CREATE MATERIALIZED VIEW LOG ON scott.emp
WITH SEQUENCE, ROWID (JOB, DEPTNO, SAL)
INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW LOG ON scott.dept
WITH SEQUENCE, ROWID (DEPTNO)
INCLUDING NEW VALUES;

SQL> DROP MATERIALIZED VIEW scott.sal_dept_mv;

SQL> CREATE MATERIALIZED VIEW scott.sal_dept_mv
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT e.job, e.deptno, sum(e.sal)
FROM emp e,
dept d
WHERE e.deptno=d.deptno
GROUP BY e.job, e.deptno;








Create a view log for the master table
This is required for fast refresh

create materialized view log on table
/

create materialized view log on table /
or...

create materialized view log on table
tablespace tablespace_name
/


List all materialized view logs

select log_owner
, log_table
from dba_mview_logs
/


Create a simple materialized view

create materialized view andy_mview
refresh [fast | complete | force]
start with sysdate
next sysdate + 1/24
with primary key
as select * from test_table
/

Fast = update changes only
Complete = wipe and repopulate the mview
Force = fast if possible, complete if not.


Show all materialized and resfresh times

set lines 100 pages 999
col last_refresh format a20
select owner
, mview_name
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/

Show materialized view tables and masters

set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/

Show refresh jobs in dba_jobs
This is useful for spotting failures

set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/


Manually start a refresh

execute dbms_mview.refresh ('owner.mv_table');

execute dbms_mview.refresh ('owner.mv_table');


Force a complete refresh

execute dbms_mview.refresh ('owner.mv_table','C');