Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:materialized_view

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:materialized_view [2016/04/06 17:06]
gpipperr [Materialized View]
prog:materialized_view [2016/04/06 17:08] (aktuell)
gpipperr
Zeile 1: Zeile 1:
 +====== 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
 +
 +
  
"Autor: Gunther Pipperr"
prog/materialized_view.txt · Zuletzt geändert: 2016/04/06 17:08 von gpipperr