mview管理

MView重要视图

Oracle文档中MView相关视图的链接Materialized View and Refresh Group Views。

在源数据库端的相关视图

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文档

• DBMS_MVIES
• DBMS_REFRESH

一些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 from dba_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 where object_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 [email protected] o
  WHERE EXISTS (SELECT * FROM [email protected] 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 FROM MV_CAPABILITIES_TABLE;

查询MView刷新延时

set linesize 130
column interval format 999999
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:236233.1 Materialized View Refresh : Log Population and Purge
• Note 258634.1 Materialized View registration at Master Site
• Note 236292.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管理实例