Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== 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