prog:materialized_view
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
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; | ||
+ | </ | ||
+ | |||
+ | View anlegen und regelmäßig refreschen | ||
+ | <code plsql> | ||
+ | connect info@dev | ||
+ | |||
+ | PROMPT Delete Materialized View ' | ||
+ | DROP MATERIALIZED VIEW mv_persid_firmengrp; | ||
+ | |||
+ | PROMPT Creating Materialized View ' | ||
+ | |||
+ | |||
+ | CREATE MATERIALIZED VIEW mv_persid_firmengrp | ||
+ | TABLESPACE info_data | ||
+ | PARALLEL 16 | ||
+ | BUILD IMMEDIATE | ||
+ | REFRESH FORCE | ||
+ | WITH PRIMARY KEY | ||
+ | AS | ||
+ | SELECT | ||
+ | , | ||
+ | FROM firmen fma | ||
+ | | ||
+ | | ||
+ | --- Statement usw. | ||
+ | / | ||
+ | |||
+ | -- Refresh testen | ||
+ | begin | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | -- Job anlegen | ||
+ | DECLARE | ||
+ | X NUMBER; | ||
+ | BEGIN | ||
+ | SYS.DBMS_JOB.SUBMIT | ||
+ | ( | ||
+ | job => X | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | , | ||
+ | ); | ||
+ | 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 => [[prog: | ||
+ | <code plsql> | ||
+ | select * from V$VPD_POLICY; | ||
+ | -- | ||
+ | select count(*) from personen; | ||
+ | --- | ||
+ | select * from V$VPD_POLICY; | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== 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 | ||
+ | | ||
+ | name => ' | ||
+ | list => ' | ||
+ | next_date => null, | ||
+ | interval => '', | ||
+ | implicit_destroy => FALSE, | ||
+ | rollback_seg => '', | ||
+ | push_deferred_rpc => TRUE, | ||
+ | refresh_after_errors => FALSE); | ||
+ | END; | ||
+ | / | ||
+ | BEGIN | ||
+ | | ||
+ | name => ' | ||
+ | , list => ' | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | ---------- | ||
+ | -- Job wieder entfernen, wird später von Hand gesteuert | ||
+ | - | ||
+ | select * from user_jobs where UPPER(what) | ||
+ | |||
+ | set serveroutput on | ||
+ | -- delete job if not nessesary! | ||
+ | declare | ||
+ | v_job_id number; | ||
+ | begin | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | exception | ||
+ | when NO_DATA_FOUND then | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | ---------- | ||
+ | -- refreschen | ||
+ | exec dbms_refresh.refresh(name => ' | ||
+ | |||
+ | ---------- | ||
+ | -- wieder entfernen | ||
+ | |||
+ | exec dbms_refresh.destroy(name => ' | ||
+ | |||
+ | ---------- | ||
+ | </ | ||
+ | ====Quellen ==== | ||
+ | |||
+ | SQL Guide | ||
+ | * https:// | ||
+ | |||
+ | Database Data Warehousing Guide - Basic Materialized Views | ||
+ | * https:// | ||
+ | |||
+ | |||
prog/materialized_view.txt · Zuletzt geändert: 2016/04/06 17:08 von gpipperr