Oracle Notes : MATERIALIZED VIEWS - SNAPSHOT

- $ORACLE_HOME/rdbms/admin/catsnap.sql & dbmssnap.sql

- dbms_mview, dbms_olap

, dbms_job

- data dict:

DBA_MVIEWS, DBA_SNAPSHOTS, DBA_SNAPSHOT_LOGS, DBA_REGISTERED_SNAPSHOTS,

DBA_REFRESH, DBA_RGROUP,

DBA_JOBS

- errors to alert log and trace files with ORA-12012

- for automatic mview refresh job_queue_processes must be > 0

- snapshots are automatically registered at the master site:

use sys.dba_registered_snapshots at master site to see registered snapshots

- grant create materialized view to ;

- mview types: primary key, rowid, subquery

- refresh types:

- fast (incremental):

- needs mview log

- if mview is based on multiple tables, each table should have a mview log

- exec dbms_mview.refresh('', 'F');

- complete:

- fkeys referencing the mview must be disabled before refresh

- if snapshot log: exec dbms_mview.purge_log('', 1);

- exec dbms_mview.refresh('', 'C');

- snapshot logs - materialized view log:

- always create snap log before snapshot

- mlog$_ prefixes

- each log associated with a single master table

- each log can support multiple snapshots on the master table

- export/import:

snapshots and snap logs are exported with schema name explicitly set in DDL,

therefore imports cannot be performed into different schemas.

Even fromuser/touser will fail.

- primary key mview:

- mview log:

create materialized view log on .

tablespace snap_log

storage (initial 500k next 500k pctincrease 0)

with primary key;

- mview:

create materialized view .

tablespace snap_data

storage (initial 500k next 500k pctincrease 0)

using index

tablespace snap_index

storage (initial 500k next 500k pctincrease 0)

refresh fast on demand as

select * from .

@;

- rowid mview:

- mview log:
     create materialized view log on customer
     tablespace snap_log
     nologging
     storage (initial 50k next 50k pctincrease 0) with rowid;
- mview
     create materialized view customer
     tablespace snap_data
     storage (initial 100k next 100k pctincrease 0)
     refresh fast on demand with rowid as
     select * from customer@proddb;

- refresh groups:

- mviews in group are refreshed at the same time to ensure

transactionally consistent data

- mview group refreshes done via delete/re-insert (instead of truncate/re-insert)

so other users will not see a gap where the mview is empty

- there must always be 2 mviews in a refresh group. So a dummy mview must be created

if the group exists just to avoid the "no data found" gap during refresh.

- exec dbms_refresh.make(name => 'events_group',

list => 'events_mv,events_dual_mv',

next_date => sysdate,

interval => 'sysdate + (30/1440)',

implicit_destroy => FALSE);

- removing a refresh group

exec dbms_refresh.destroy('');
- steps for rebuilds after schema changes:

1. TARGET: find grantee's granted table privileges on

2. TARGET: drop materialized view

3. SOURCE: drop materialized view log on

4. SOURCE: create materialized view log on

5. TARGET: create materialized view

6. TARGET: grant select on to

- updateable snapshots:

CREATE SNAPSHOT FOR UPDATE AS SELECT * FROM @;

- query rewrite:

- disabled by default, must enable with ... enable query rewrite ...

- init parameters:

query_rewrite_enabled = true

query_rewrite_integrity = trusted

job_queue_processes = >= 1

job_queue_interval = ?

optimizer_mode = choose, first_rows, or all_rows

compatible = >= 8.1

- grants: grant rewrite; OR grant global rewrite;

- dbms_olap: mview analysis package

- estimate mview size

- recommend mviews

- report on mview usage

- dimensions: hierarchial info about data

- Oracle7 to Oracle8 problem: ORA-12028

create snapshots with 'refresh complete with rowid'

reference

===============

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51662/，如需转载，请注明出处，否则将追究法律责任。

上一篇： RMAN--backup database plus archivelog內部執行步驟

下一篇： RAC--Step-By-Step Installation of 9.2.0.5 RAC on Linux

请登录后发表评论登录

全部评论

vongates

注册时间：2018-09-11

博文量
449
访问量

320900

ITPub博客

MV--MATERIALIZED VIEWS(>8i) or SNAPSHOTS(

最新文章