ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图日志无法正常清除的解决方法

物化视图日志无法正常清除的解决方法

原创 Linux操作系统 作者:space6212 时间:2019-03-28 08:30:06 0 删除 编辑

今天在检查时,发现某个物化视图日志占用的空间超过150M,再检查看,该物化视图日志表的记录数有150W,由于其对应的物化视图没有会刷新一次,结合业务量分析可知:物化视图日志不能正常清除。

下面的解决步骤


--在源库查询物化视图对应日志条目个数
SQL> select count(1) from MLOG$_ITEM_TAG;

COUNT(1)
----------
532515

--在物化视图端刷新物化视图
SQL> exec dbms_snapshot.refresh('item_tag');

PL/SQL procedure successfully completed

--返回源库查询物化视图对应日志条目个数,发现日志并没有被清除
SQL> select count(1) from MLOG$_ITEM_TAG;

COUNT(1)
----------
532515

--在源库查询ITEM_TAG对应的注册信息,发现有两个库的物化视图是基于ITEM_TAG建立的
SQL> select * from USER_REGISTERED_MVIEWS where name='ITEM_TAG';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------
TEST ITEM_TAG SC1.SOUCHANG.COM YES YES PRIMARY KEY 54 ORACLE 8 MATERIALIZED VIEW SELECT "ITEM_TAG"."ITEM_TAG_ID" "ITEM_TAG_ID","ITEM_TAG"."ITEM_TAG_SEQ_NUMBER" "
TEST ITEM_TAG SC2TEST.SOUCHANG.COM YES YES PRIMARY KEY 86 ORACLE 8 MATERIALIZED VIEW SELECT "ITEM_TAG"."ITEM_TAG_ID" "ITEM_TAG_ID","ITEM_TAG"."ITEM_TAG_SEQ_NUMBER" "

SQL> select * from DBA_BASE_TABLE_MVIEWS where master='ITEM_TAG';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------
SOUCHANG2 ITEM_TAG 2006-06-22 上午 08:54:0 54
SOUCHANG2 ITEM_TAG 2006-07-17 上午 10:47:5 86

/*原因找出来了,是因为其中一个库的物化视图没有刷新,所以导致物化视图日志没有被删除(物化视图日志必须在所有基于该表的物化视图都刷新后才会被删除)
遇到这种情况可以有两种解决方法:删除无法刷新的物化视图或删除无法刷新的物化视图注册信息
在本案例中,由于无法刷新物化视图的库是一个老库,已经被移除了,所以只能通过在源库删除这些物化视图的注册信息
*/
SQL> exec DBMS_MVIEW.unregister_mview('TEST','ITEM_TAG','SC1.SOUCHANG.COM');

PL/SQL procedure successfully completed

--删除的MVIEW_ID应该是不需要的MVIEW对应的ID
SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(54);

PL/SQL procedure successfully completed

/*
--注意:千万不能把MVIEW_ID=86的MVIEW LOG删除了;如果删除的是MVIEW_ID=86的物化视图注册信息的话,在物化视图端刷新会报错,此时只能重建物化视图
SQL> exec dbms_snapshot.refresh('item_tag');

begin dbms_snapshot.refresh('item_tag'); end;

ORA-12034: materialized view log on "SOUCHANG2"."ITEM_TAG" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
*/

--此时在可刷新端刷新物化视图
SQL> exec dbms_snapshot.refresh('item_tag');

PL/SQL procedure successfully completed

--此时源库上ITEM_TAG对应的物化视图日志被清除
SQL> SELECT COUNT(1) FROM MLOG$_ITEM_TAG;

COUNT(1)
----------
0

/*
如果废弃的物化视图端的数据库仍然可用,且有相关的数据库链接,则更简单的办法是在废弃物化视图的数据库中把物化视图删除,此时如果数据库链接可用,oracle会把源数据库端的物化视图注册信息一并删除,如:
*/
--首先在源数据库中查询名称为BRAND的物化视图注册信息
SQL> select * from DBA_REGISTERED_MVIEWS where name='BRAND';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------
FIREDRAKE BRAND NEI.SOUCHANG.COM YES YES PRIMARY KEY 1 ORACLE 8 MATERIALIZED VIEW SELECT "BRAND"."BRAND_ID" "BRAND_ID","BRAND"."ORGANIZATION_ID" "ORGANIZATION_ID"

SQL> select * from DBA_BASE_TABLE_MVIEWS where master='BRAND';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------
FIREDRAKE BRAND 2006-07-17 上午 08:31:2 1

--然后在物化视图端执行:
SQL> DROP MATERIALIZED VIEW BRAND;

Materialized view dropped

--此时源数据库端BRAND对应的物化视图注册信息已经被删除了
SQL> select * from DBA_BASE_TABLE_MVIEWS where master='BRAND';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------

SQL> select * from DBA_REGISTERED_MVIEWS where name='BRAND';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------

/*
当然,这种做法要符合三个前提条件
1、废弃的物化视图端数据库仍然可用
2、网络正常,在物化视图端能用dblink能访问源数据库
3、在业务上物化视图可以被删除
*/

最后,在数据库空闲的时候对物化视图日志表执行move操作,降低HWM

alter table mlog$_item_tag move;

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

下一篇: outline初体验
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168803