oracle无法删除物化视图日志,ORACLE物化视图--物化视图日志没有清除问题、日志清除方法...

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

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

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.