====== Materialized View in der Oracle Datenbank verwenden  ======

**ab min. 8.0 **

**Aktualisert April 2016**

Rechte:
<code plsql>
connect system@dev
--recht vergeben
grant create snapshot to suginfo;
</code>

View anlegen und regelmäßig refreschen
<code plsql>
connect info@dev

PROMPT Delete Materialized View 'mv_persid_firmengrp'
DROP MATERIALIZED VIEW mv_persid_firmengrp;

PROMPT Creating Materialized View 'mv_persid_firmengrp' 


CREATE MATERIALIZED VIEW mv_persid_firmengrp
TABLESPACE info_data
PARALLEL 16
BUILD IMMEDIATE
REFRESH FORCE
WITH PRIMARY KEY
AS
SELECT  fgt.psn_id AS persid
       ,fgr.NAME AS firmengruppe      
  FROM firmen fma
   WHERE 
   ......
   --- Statement usw.
/

-- Refresh testen
begin
 dbms_snapshot.REFRESH('mv_persid_firmengrp');
end;
/

-- Job anlegen
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'begin  dbms_snapshot.REFRESH(''mv_persid_firmengrp''); end;'
     ,next_date  => sysdate
     ,interval   => 'SYSDATE+60/1440 '
     ,no_parse   => FALSE
    );
END;
commit;

</code>
Probleme\\
ORA-30372: fine grain access policy conflicts with materialized view\\
Tabelle Personen darf nicht enthalten sein, da RLS auf der Tabelle\\
Verwendung pürfen: siehe => [[prog:virtual_private_database|Virtual Private Database (VPD)]]
<code plsql>
select * from V$VPD_POLICY;
--
select count(*) from personen;
---
select * from V$VPD_POLICY;
</code>

----

==== Refresh Gruppe anlegen ====

Beispiel Code:
<code sql>

----------
-- mv's

create materialized view t1 as select * from user_objects;
create materialized view t2 as select * from user_objects;
create materialized view t3 as select * from user_objects;

----------
-- anlegen
-- Mit Job der nie ausgeführt wird
BEGIN
   DBMS_REFRESH.MAKE (
      name => 'REFRESH_GRP_GPI',
      list => 'T1', 
      next_date => null, 
      interval => '',
      implicit_destroy => FALSE, 
      rollback_seg => '',
      push_deferred_rpc => TRUE, 
      refresh_after_errors => FALSE);
END;
/
BEGIN
   DBMS_REFRESH.ADD (
        name      => 'REFRESH_GRP_GPI'
      , list      => 'T2,T3'  
   );   
end;
/

----------
-- Job wieder entfernen, wird später von Hand gesteuert
-
select * from user_jobs where UPPER(what)  like '%REFRESH_GRP_GPI%';

set serveroutput on
-- delete job if not nessesary!
declare
v_job_id number;
begin
 select job into v_job_id from user_jobs where UPPER(what)  like '%REFRESH_GRP_GPI%' ;
 dbms_job.remove( JOB => v_job_id);
 dbms_output.put_line('-- Info :: delete job id'||to_char(v_job_id));
 commit;
exception 
  when NO_DATA_FOUND then
   dbms_output.put_line('-- Error ::'||SQLERRM);
end;
/

----------
-- refreschen
exec dbms_refresh.refresh(name => 'REFRESH_GRP_GPI');

----------
-- wieder entfernen

exec dbms_refresh.destroy(name => 'REFRESH_GRP_GPI');

----------
</code>
====Quellen ====

SQL Guide
  * https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302

Database Data Warehousing Guide - Basic Materialized Views
  * https://docs.oracle.com/database/121/DWHSG/basicmv.htm#DWHSG008