====== 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 => [[prog:virtual_private_database|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
* 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