prog:materialized_view
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
prog/materialized_view.txt · Zuletzt geändert: 2016/04/06 17:08 von gpipperr