Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:materialized_view [2016/04/06 17:06] – [Materialized View] gpipperr
+++ 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