查询oracle物化视图,ORACLE物化视图

MView重要视图

在源数据库端的相关视图

DBA_BASE_TABLE_MVIEWS

此视图与系统视图SYS.SLOG$相对应。

视图DBA_BASE_TABLE_MVIEWS记录了使用MView Log访问基表的相关刷新的信息。换句话说就是记录了使用了MView Log并且做过快速刷新的MView的信息，必须同时满足有MView Log以及做过快速刷新这两个条件，缺一不可。

列名描述

OWNER

基表的OWNER

MASTER

基表的名称

MVIEW_LAST_REFRESH_TIME

基于这个基表的MView最后的快速刷新的时间

MVIEW_ID

基于这个基表的MView在数据库中的ID，这个可以和DBA_REGISTERED_MVIEWS关联找出相应的MView的名称

备注：SYS.SLOG$的作用SYS.SLOG$记录了注册在源数据库端的能进行快速刷新的MView的信息。这个信息在进行快速刷新的时候会备用到，同时也会用来维护基表的MView Log信息。

DBA_REGISTERED_MVIEWS

此视图与系统视图SYS.REG_SNAP$相对应。

视图DBA_REGISTERED_MVIEWS记录了所有在主站点上面注册的MView的相关的信息，这里保存的东西只是起提供信息性的作用。当MView在创建的时候Oracle会自动的将MView的信息写入到此视图中，但是也不排除会有失败的情况，要是失败了的话需要手动的使用DBMS_MVIEW包中的REGISTER_MVIEW进行注册。

详细的列描述就不列了，具体的参看文档。

备注：SYS.REG_SNAP$的作用SYS.REG_SNAP$记录了注册在源数据库端的MView的信息，这个表只是为了提供信息而用。

DBA_MVIEW_LOGS

此视图与系统视图SYS.mlog$相对应。

此视图记录了源数据库中的所有的MView Log的相关信息。

详细的列描述就不列了，具体的参看文档。

在MView数据库端的相关视图

DBA_MVIEWS

此视图与系统视图SYS.SNAP$相对应。

此视图我们在之前已经接触了很多了，视图里面保存了数据库中所有的MView的信息。我们进行的大部分物化视图的查询工作都要基于这个视图来进行。

详细的列描述见文档。

DBA_MVIEW_REFRESH_TIMES

此视图与系统视图SYS.SNAP_REFTIME$相对应。

此视图记录了MView最后刷新的时间，这在我们检查MView是否正常的刷新过时很有用的。

详细的列描述见文档。

DBA_REFRESH和DBA_REFRESH_CHILDREN

这两个视图我们在之前也已经接触过了，主要是记录了刷新组相关的信息。

MView相关包

在进行MView的维护的时候使用的最多的就是DBMS_MVIEW和DBMS_REFRESH这两个包了，前一个用来进行MView的维护，另外一个进行MView刷新组的维护。

相关的Oracle文档

一些MView维护的相关问题

SNAPSHOT vs. Materialized View

SNAPSHOT == Materialized View (老版本的Oracle中管MView叫SNAPSHOT

 )

DBMS_SNAPSHOT == DBMS_MVIEW

CREATE SNAPSHOT == CREATE MATERIALIZED VIEW

清理无效的MView Log

1. 查询MV表信息

SELECT * FROM DBA_BASE_TABLE_MVIEWS;

ALL_BASE_TABLE_MVIEWS: All materialized views with log(s) in the database that the user can see

2. 查询已注册的快照信息

col owner for a15

col name for a30

col snapshot_site for a15

col refresh_method for a15

select owner, name, snapshot_site, refresh_method fromdba_registered_snapshots;

3. 对比上面两个结果找出很久没有刷新的MV ID，然后进行UNREGISTER_MVIEW操作

EXEC DBMS_MVIEW.UNREGISTER_MVIEW(mviewowner, mviewname, mviewsite);

mviewowner: MView的OWNER。

mviewname: MView的名字。

mviewsite: 这个可以从DBA_REGISTERED_MVIEWS中的MVIEW_SITE中看到。

4. 清理MVLOG记录

SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(MVID);

查询MView Log的大小

set serveroutput on

declare v_output number;

begin

dbms_output.put_line(‘Owner                MV Table Name                            MV Log Size   ‘);

dbms_output.put_line(‘——————– —————————————- ————–‘);

for c_cursor in (select owner, object_name from all_objects whereobject_name like ‘MLOG$_%’) LOOP

execute immediate ‘select count(*) from ‘ || c_cursor.owner||’.’|| c_cursor.object_name into v_output;

dbms_output.put_line(

rpad(substr(c_cursor.owner,0,20),20,’ ‘)||’ ‘

||rpad(substr(c_cursor.object_name,7,40),40, ‘ ‘)

||’ ‘||v_output);

end loop;

end;

/

set serveroutput off

检查MV的刷新兼容性

— build table MV_CAPABILITIES_TABLE

SQL> @?/rdbms/admin/utlxmv.sql

— explain mv

TRUNCATE TABLE MV_CAPABILITIES_TABLE;

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW(‘mv_t3’);

— Or like this

TRUNCATE TABLE MV_CAPABILITIES_TABLE;

BEGIN

DBMS_MVIEW.EXPLAIN_MVIEW (‘SELECT * FROM o

WHERE EXISTS (SELECT * FROM c

WHERE o.customer_id = c.customer_id AND c.credit_limit > 500)’);

END;

/

— 查看结果

COL MVOWNER FOR A10

COL MVNAME FOR A20

COL MSGTXT FOR A65

SELECT MVOWNER,MVNAME,CAPABILITY_NAME,POSSIBLE,MSGTXT FROMMV_CAPABILITIES_TABLE;

查询MView刷新延时

set linesize 130

column interval format 

column “rgroup owner” format a20

column “refresh group” format a20

column “minutes behind” format 9999.99

column “master link” format a20

column “mview owner” format a15

column next_date format a20

SELECT

—    int.rowner “rgroup owner”,

—    int.rname “refresh group”,

mv.owner as “mview owner”,

mv.mview_name as “mview name”,

mv.master_link as “master link”,

round(1440*(sysdate – mv.last_refresh_date)) as “minutes behind”,

to_char(int.next_date, ‘yyyy-mm-dd hh24:mi:ss’) “next date”,

round(int.interval*1440) “interval”

FROM dba_mviews mv,

(

SELECT child.owner, child.name, child.rowner, child.rname,job.next_date, job.next_date – job.last_date as interval

FROM dba_refresh ref, dba_refresh_children child, dba_jobs job

WHERE ref.rname = child.rname AND ((upper(job.what) LIKE’%’||ref.rname||’%’)

OR (upper(job.what) LIKE ‘%’||ref.rname||’%’))

) int

WHERE mv.owner = int.owner(+) AND mv.mview_name = int.name(+)

ORDER BY (sysdate – mv.last_refresh_date) * 1440 DESC, mv.owner,mv.mview_name;

参考文档

Note:.1 Materialized View Refresh : Log Population and Purge

Note .1 Materialized View registration at Master Site

Note .1 Scripts to Report Information about Materialized View Logs at the Master Site.

8 Managing a Materialized View Replication Environment

来自Advanced Replication Management API Reference，很有用的MView管理实例