关闭

物化视图日志没有清除

标签: tablesqloutput数据库服务器脚本
3254人阅读 评论(0) 收藏 举报
分类:
转自:http://yangtingkun.itpub.net/post/468/19939

最近在研究物化视图日志的结果,查询了一下系统中物化视图日志的情况,没想到却发现了一个问题:

 调用自己写的一个脚本,结果发现个别物化视图日志的数量已经达到了几十万:

SQL> declare
  2  v_output number;
  3  begin
  4  for c_cursor in (select tname from tab where tname like 'MLOG%') LOOP
  5  execute immediate 'select count(*) from ' || c_cursor.tname into v_output;
  6  dbms_output.put_line(substr(c_cursor.tname, 7) || ' count(*) is ' || v_output);
  7  end loop;
  8  end;
  9  /
CAT_ADMIN count(*) is 0
CAT_AGENT count(*) is 0
CAT_AREA_MEDICARE count(*) is 115416
CAT_AREA_PRICE count(*) is 789700
……
CAT_PRODUCT count(*) is 507262
CAT_PRODUCT_CENTER count(*) is 90945
……
PLT_ORG_PLAT count(*) is 207
PLT_PLAT count(*) is 52
T_DIRECT_SEEDING count(*) is 0
T_INVITE count(*) is 0
T_MARK count(*) is 0
Z_INVITE_COMM_PUB count(*) is 0

PL/SQL procedure successfully completed.

当前查询的站点是主站点,建立了一个主体组。系统中还建立了三个物化视图站点,其中两个刷新全部主体组,一个刷新主体组的一部分。其中两个物化视图刷新组的刷新频率是30分钟,另外一个是1天。因此,物化视图日志中存在少量数据是正常的,但是超过十万条就不正常了。

查询数据量大的一个物化视图日志表,发现物化视图日志中包含着很多以前的数据,时间从2004年8月一直到现在,而物化视图站点最大刷新间隔是1天。

通过查询DBA_BASE_TABLE_VIEWS视图,发现问题:

SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MVIEW_LAST_REFRESH_TIME < TRUNC(SYSDATE);

OWNER   MASTER                         MVIEW_LAST_REFRESH_   MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN  CAT_AREA_MEDICARE              2004-08-08 00:57:26        448
NDMAIN  CAT_AREA_PRICE                 2004-08-08 00:57:26        449
NDMAIN  CAT_AUTH_MEDICARE              2004-08-08 00:57:26        450
NDMAIN  CAT_AUTH_MEDICARE_RELATE_PRODU 2004-08-08 00:57:26        451
NDMAIN  CAT_AUTH_PRICE                 2004-08-08 00:57:26        469
NDMAIN  CAT_BUYER                      2004-08-08 00:57:26        509
NDMAIN  CAT_CATEGORY                   2004-08-08 00:57:26        529
NDMAIN  CAT_DEALER                     2004-08-08 00:57:26        473
NDMAIN  CAT_DOSEAGE_FORM               2004-08-08 00:57:26        454
NDMAIN  CAT_DRUG                       2004-08-08 00:57:26        455
NDMAIN  CAT_ENTERPRISE                 2004-08-08 00:57:26        456
NDMAIN  CAT_ENTERPRISE_PLATCODE        2004-08-08 00:57:26        457
NDMAIN  CAT_FILE_SALER                 2004-08-08 00:57:26        474
NDMAIN  CAT_INVITE_COMM                2004-08-08 00:57:26        489
NDMAIN  CAT_MANUFACTURE                2004-08-08 00:57:26        471
NDMAIN  CAT_METRIC                     2004-08-08 00:57:26        458
NDMAIN  CAT_PRODUCT                    2004-08-08 00:57:26        534
NDMAIN  CAT_PRODUCT_CENTER             2004-08-08 00:57:26        475
NDMAIN  CAT_QUALITY_DEFINE             2004-08-08 00:57:26        461
NDMAIN  CAT_REGION                     2004-08-08 00:57:26        462
NDMAIN  CAT_WRAP                       2004-08-08 00:57:26        472
NDMAIN  MBR_FOOTING_RECORD             2004-08-08 00:57:26        463
NDMAIN  MBR_GRADE                      2004-08-08 00:57:26        464
NDMAIN  MBR_MEMBER                     2004-08-08 00:57:26        465
NDMAIN  MBR_MEMBER_ITEM                2004-08-08 00:57:26        466
NDMAIN  PLT_ORG_PLAT                   2004-08-08 00:57:26        467
NDMAIN  PLT_PLAT                       2004-08-08 00:57:26        468

27 rows selected.

这些物化视图的查询上次刷新时间是04年8月,而且也正是这些物化视图的物化视图日志数据量异常。

继续查询,又发现问题:

SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';

OWNER   MASTER                         MVIEW_LAST_REFRESH_   MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN  CAT_PRODUCT                    2004-08-08 00:57:26        534
NDMAIN  CAT_PRODUCT                    2005-02-28 01:05:59        560
NDMAIN  CAT_PRODUCT                    2005-02-28 13:34:42        676
NDMAIN  CAT_PRODUCT                    2005-02-28 14:08:46        530

居然有四个物化视图定义在物化视图组上,看来是其中一个一直没有刷新,难怪造成了这个问题。

SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID FROM DBA_REGISTERED_MVIEWS
  2  WHERE NAME = 'CAT_PRODUCT';

OWNER   NAME            MVIEW_SITE                  MVIEW_ID
------- --------------- ------------------------- ----------
NDMAIN  CAT_PRODUCT     REPDB01.US.ORACLE.COM            560
NDMAIN  CAT_PRODUCT     BJDB01.US.ORACLE.COM             530
NDMAIN  CAT_PRODUCT     ZJTRADE.US.ORACLE.COM            676

可是查询已注册的物化视图却只发现三个,看来原因就出在多出的那个物化视图身上。

其中一个物化视图站点在去年8月的时候进行过升级,整个数据库EXP,导入到一个新的服务器上,并重建了物化视图站点。看来现在的问题是由于上次没有将物化视图环境清除干净。

物化视图以前已经进行了UNREGISTERED_MVIEW_GROUP的操作,只需要执行PURGE_MVIEW_FROM_LOG过程就行了。

PURGE_MVIEW_FROM_LOG过程进行了重载,可以输入MVIEW_ID或输入MVIEWOWNER、MVIEWNAME、MVIEWSITE。由于MVIEWSITE现在已经被重复使用,因此只能通过MVIEW_ID的方式。

SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';

OWNER   MASTER                         MVIEW_LAST_REFRESH_   MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN  CAT_PRODUCT                    2004-08-08 00:57:26        534
NDMAIN  CAT_PRODUCT                    2005-02-28 01:05:59        560
NDMAIN  CAT_PRODUCT                    2005-02-28 14:08:46        530
NDMAIN  CAT_PRODUCT                    2005-02-28 14:34:39        676

SQL> SELECT COUNT(*) FROM NDMAIN.MLOG$_CAT_PRODUCT;

  COUNT(*)
----------
    507347

SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(534)

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';

OWNER   MASTER                         MVIEW_LAST_REFRESH_   MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN  CAT_PRODUCT                    2005-02-28 01:05:59        560
NDMAIN  CAT_PRODUCT                    2005-02-28 14:08:46        530
NDMAIN  CAT_PRODUCT                    2005-02-28 14:34:39        676

SQL> SELECT COUNT(*) FROM NDMAIN.MLOG$_CAT_PRODUCT;

  COUNT(*)
----------
       236

通过执行PURGE_MVIEW_FROM_LOG过程,DBA_BASE_TABLE_MVIEWS视图中的结果和MLOG中的数量都恢复正常。

SQL> begin
  2  for i in (select mview_id from dba_base_table_mviews where mview_last_refresh_time < trunc(sysdate)) loop
  3  dbms_mview.purge_mview_from_log(i.mview_id);
  4  end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

注:
DBA_BASE_TABLE_MVIEWS在低版本没有这个视图。
可以用sys.slog$代替。
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:59636次
    • 积分:800
    • 等级:
    • 排名:千里之外
    • 原创:12篇
    • 转载:38篇
    • 译文:1篇
    • 评论:4条
    文章分类
    文章存档
    最新评论
    五花八门