Inhaltsverzeichnis

Materialized View in der Oracle Datenbank verwenden

ab min. 8.0

Aktualisert April 2016

Rechte: 

CONNECT system@dev
--recht vergeben
grant CREATE snapshot TO suginfo;

View anlegen und regelmäßig refreschen 

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;

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 ⇒ Virtual Private Database (VPD) 

SELECT * FROM V$VPD_POLICY;
--
SELECT COUNT(*) FROM personen;
---
SELECT * FROM V$VPD_POLICY;

Refresh Gruppe anlegen

Beispiel Code: 

----------
-- 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');
 
----------

Quellen

SQL Guide

Database Data Warehousing Guide - Basic Materialized Views