oracle 物化视图日志不自动清除

缺乏、安全感 2022-10-01 13:53 439阅读 0赞

清理[物化视图][Link 1]日志步骤：  
以scott用户下的一张mview\_1为基表建立两个物化视图，然后unregister其中一个(EXEC DBMS\_MVIEW.UNREGISTER\_MVIEW('MING', 'TARGET\_MVIEW\_1', 'OGG1')![baiy.gif][]。向基表中出入三条数据，提交后查看日志表：  
创建dblink  
create database link scott\_link\_1  
connect to scott  
identified by [oracle][]  
using 'systest';  
  
create database link scott\_link\_2  
connect to scott  
identified by oracle  
using 'systest';  
  
scott用户创建物化视图日志：  
SQL> sho user  
USER is "SCOTT"  
SQL> desc mview\_1  
Name                                      Null?    Type  
\----------------------------------------- -------- ----------------------------  
ID                                        NOT NULL NUMBER  
NAME                                               VARCHAR2(20)  
  
SQL> create materialized view log on mview\_1 with primary key;  
  
Materialized view log created.  
  
SQL> select table\_name from user\_tables where table\_name like 'MLOG$%';  
  
TABLE\_NAME  
\------------------------------  
MLOG$\_MVIEW\_1  
  
创建物化视图：  
SQL> sho user  
USER is "MING"  
SQL> create materialized view target\_mview\_1 on prebuilt table refresh fast on demand with primary key start with sysdate next  
  2  to\_date(concat(to\_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as  
  3  select \* from scott.mview\_1@scott\_link\_1;  
  
Materialized view created.  
  
SQL> create materialized view target\_mview\_2 build immediate refresh fast on demand with primary key start with sysdate next  
  2  to\_date(concat(to\_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as  
  3  select \* from scott.mview\_1@scott\_link\_2;  
  
Materialized view created.  
  
scott用户向mview\_1中插入4条数据，然后查询日志表：  
  
SQL> select count(\*) from scott.MLOG$\_MVIEW\_1;  
  
  COUNT(\*)  
\----------  
         4  
存在了4条记录。  
  
物化视图日志存储在"MLOG$\_"为开头的表名中。  
  
首先查看有多少物化视图注册到了刷新机制中  
SQL> select OWNER,NAME,MVIEW\_SITE,MVIEW\_ID from DBA\_REGISTERED\_MVIEWS;  
  
OWNER           NAME                           MVIEW\_SITE                       MVIEW\_ID  
\--------------- ------------------------------ ------------------------------ ----------  
SYSMAN          MGMT\_ECM\_MD\_ALL\_TBL\_COLUMNS    SEEDDATA                                0  
SH              CAL\_MONTH\_SALES\_MV             OGG1                                   21  
SH              FWEEK\_PSCAT\_SALES\_MV           OGG1                                   22  
MING            TARGET\_MVIEW\_1                 OGG1                                   87  
MING            TARGET\_MVIEW\_2                 OGG1                                   88  
  
现在删掉一条db\_link：  
SQL> drop database link scott\_link\_2;  
  
Database link dropped.  
  
  
  
查看基表上的物化视图刷新依赖  
SQL> SELECT \* FROM DBA\_B[ASE][]\_TABLE\_MVIEWS;  
  
OWNER           MASTER                         MVIEW\_LAST\_REFRESH\_   MVIEW\_ID  
\--------------- ------------------------------ ------------------- ----------  
SCOTT           MVIEW\_1                        2018-01-21 04:00:02         87  
SCOTT           MVIEW\_1                        2018-01-21 04:06:12         88  
  
  
  
增量刷新两个物化视图  
SQL> exec dbms\_mview.refresh('TARGET\_MVIEW\_1');  
  
PL/SQL procedure successfully completed.  
  
SQL> exec dbms\_mview.refresh('TARGET\_MVIEW\_2');  
BEGIN dbms\_mview.refresh('TARGET\_MVIEW\_2'); END;  
  
\*  
ERROR at line 1:  
ORA-02019: connection description for remote database not found  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2558  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2771  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2740  
ORA-06512: at line 1  
  
这时候TARGET\_MVIEW\_2已经不能刷新了；  
  
物化视图日志：  
SQL> select count(\*) from scott.MLOG$\_MVIEW\_1;  
  
  COUNT(\*)  
\----------  
         4  
正常来说，基于一张表的所有物化视图刷新以后日志表会被清空，现在因为有一个物化视图因为db\_link删除而导致刷新失败，物化视图日志是不会自动清除的。  
这时候的解决思路通常是：  
1.删除无法刷新的物化视图  
2.删除无法刷新的物化视图的注册信息  
  
这里我采用第二种方法；  
SQL> EXEC DBMS\_MVIEW.UNREGISTER\_MVIEW('MING', 'TARGET\_MVIEW\_2', 'OGG1');  
  
PL/SQL procedure successfully completed.  
  
SQL> select OWNER,NAME,MVIEW\_SITE,MVIEW\_ID from DBA\_REGISTERED\_MVIEWS;  
  
OWNER           NAME                           MVIEW\_SITE                       MVIEW\_ID  
\--------------- ------------------------------ ------------------------------ ----------  
SYSMAN          MGMT\_ECM\_MD\_ALL\_TBL\_COLUMNS    SEEDDATA                                0  
SH              CAL\_MONTH\_SALES\_MV             OGG1                                   21  
SH              FWEEK\_PSCAT\_SALES\_MV           OGG1                                   22  
MING            TARGET\_MVIEW\_1                 OGG1                                   87  
可以看到MVIEW\_ID=88的物化视图已经没有了。  
  
清除日志，注意千万不要写错mview\_id，不然后续刷新会报错。  
SQL> EXEC DBMS\_MVIEW.PURGE\_MVIEW\_FROM\_LOG(88);  
  
PL/SQL procedure successfully completed.  
  
这是查询日志：  
SQL> select count(\*) from scott.MLOG$\_MVIEW\_1;  
  
  COUNT(\*)  
\----------  
         0  
发现物化视图日志已经清空了。  
  
基表再插入一条数据：  
SQL> /  
  COUNT(\*)  
\----------  
         1  
SQL> exec dbms\_mview.refresh('TARGET\_MVIEW\_1');  
  
PL/SQL procedure successfully completed.  
  
再次查询发现物化视图日志，已经自动被清除了：  
SQL> /  
  
  COUNT(\*)  
\----------  
         0  
再次查询刷新时间：  
SQL> SELECT \* FROM DBA\_BASE\_TABLE\_MVIEWS;  
  
OWNER           MASTER                         MVIEW\_LAST\_REFRESH\_   MVIEW\_ID  
\--------------- ------------------------------ ------------------- ----------  
SCOTT           MVIEW\_1                        2018-01-21 04:26:20         87  
已经没有88那条信息了。  
当然了，表还是在的，只是数据不对了。  
SQL> select count(\*) from TARGET\_MVIEW\_2;  
  
  COUNT(\*)  
\----------  
         9  
这时重建一下删除的db\_link，再尝试刷新一下TARGET\_MVIEW\_2：  
SQL> exec dbms\_mview.refresh('TARGET\_MVIEW\_2');  
BEGIN dbms\_mview.refresh('TARGET\_MVIEW\_2'); END;  
  
\*  
ERROR at line 1:  
ORA-12034: materialized view log on "SCOTT"."MVIEW\_1" younger than last refresh  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2558  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2771  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2740  
ORA-06512: at line 1  
  
还是报错，报错跟SYS.DBMS\_SNAPSHOT有关；DBMS\_MVIEW是DBMS\_SNAPSHOT的同义词，所以应该是清楚了注册信息的原因。  
重新注册，以下摘自官方文档：  
DBMS\_MVIEW.REGISTER\_MVIEW (  
   mviewowner  IN   VARCHAR2,  
   mviewname   IN   VARCHAR2,  
   mviewsite   IN   VARCHAR2,  
   mview\_id    IN   DATE | [BI][]NARY\_INTEGER,  
   flag        IN   BINARY\_INTEGER,  
   qry\_txt     IN   VARCHAR2,  
   rep\_type    IN   BINARY\_INTEGER := DBMS\_MVIEW.REG\_UNKNOWN);  
但是按照官方文档的写法总是报错：     
execute DBMS\_MVIEW.REGISTER\_MVIEW('MING', 'TARGET\_MVIEW\_2', 'OGG1',88,33, 'select \* from scott.mview\_1@scott\_link\_2', 'DBMS\_MVIEW.REG\_UNKNOWN');  
以下摘自MOS：  
How to REGISTER and UNREGISTER a Materialized View - Testcase (文档 ID 1393276.1)  
  
SQL> select s.sowner OWNER, s.vname NAME, snapid,  
  2  decode([bi][BI]tand(s.flag,1), 0, 'NO', 'YES') CAN\_USE\_LOG,  
  3  decode(bitand(s.flag,2), 0, 'NO', 'YES') UPDATABLE,  
  4  decode(bitand(s.flag,16), 16, 'ROWID',  
  5  (decode(bitand(s.flag,32), 32, 'PRIMARY KEY',  
  6  (decode(bitand(s.flag,536870912), 536870912, 'OBJECT ID',  
  7  'UNKNOWN'))))) REFRESH\_METHOD  
  8  from sys.snap$ s where s.vname='TARGET\_MVIEW\_2';  
  
OWNER           NAME                               SNAPID CAN UPD REFRESH\_METHOD  
\--------------- ------------------------------ ---------- --- --- ---------------  
MING            TARGET\_MVIEW\_2                         88 YES NO  PRIMARY KEY  
  
So if we have an mview with primary key, and NOT UPDATABLE and FAST refreshable then this will be:  
  
can\_use\_log yes 1  
updatable no 0  
primary key yes 32  
  
So we add these together and get 33 so this is the value for the FLAG column.  
  
注册：  
execute dbms\_MVIEW.register\_mview(-  
'MING', -  
'TARGET\_MVIEW\_2',-  
'OGG1',-  
88,-  
33,-  
'select \* from scott.mview\_1@scott\_link\_2');  
  
PL/SQL procedure successfully completed.  
再次查询注册信息，发现已经注册回来了，如下：  
21:19:38 SQL> col mview\_site for a20  
21:19:53 SQL> select OWNER,NAME,MVIEW\_SITE,MVIEW\_ID from DBA\_REGISTERED\_MVIEWS;  
  
OWNER                          NAME                           MVIEW\_SITE             MVIEW\_ID  
\------------------------------ ------------------------------ -------------------- ----------  
SYSMAN                         MGMT\_ECM\_MD\_ALL\_TBL\_COLUMNS    SEEDDATA                      0  
SH                             CAL\_MONTH\_SALES\_MV             OGG1                         21  
SH                             FWEEK\_PSCAT\_SALES\_MV           OGG1                         22  
MING                           TARGET\_MVIEW\_2                 OGG1                         88  
MING                           TARGET\_MVIEW\_1                 OGG1                         87  
  
5 rows selected.  
基表插入几行数据，再次尝试刷新：  
21:23:07 SQL> EXEC dbms\_mview.REFRESH('TARGET\_MVIEW\_1')  
  
PL/SQL procedure successfully completed.  
  
Elapsed: 00:00:00.37  
21:25:34 SQL> EXEC dbms\_mview.REFRESH('TARGET\_MVIEW\_2')  
BEGIN dbms\_mview.REFRESH('TARGET\_MVIEW\_2'); END;  
  
\*  
ERROR at line 1:  
ORA-12034: materialized view log on "SCOTT"."MVIEW\_1" younger than last refresh  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2558  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2771  
ORA-06512: at "SYS.DBMS\_SNAPSHOT", line 2740  
ORA-06512: at line 1  
  
这个报错也很容易理解：  
因为unregister之后，数据量变化过，我们清除来了一次物化视图日志，所以有gap，这里执行一次完全刷新应该就可以了，试一下：  
21:29:31 SQL> exec dbms\_mview.refresh('TARGET\_MVIEW\_2','C');  
  
PL/SQL procedure successfully completed.  
  
验证一下：  
基表：  
21:27:14 SQL> select count(\*) from mview\_1;  
  
  COUNT(\*)  
\----------  
        17  
  
1 row selected.  
  
物化视图：  
21:30:05 SQL> select count(\*) from target\_mview\_1 union all select count(\*) from target\_mview\_2;  
  
  COUNT(\*)  
\----------  
        17  
        17  
  
2 rows selected.  
  
在查看官方文档的时候发现有两个purge log相关的存储过程，  
DBMS\_MVIEW.PURGE\_MVIEW\_FROM\_LOG (  
   mview\_id       IN   BINARY\_INTEGER);  
  
DBMS\_MVIEW.PURGE\_MVIEW\_FROM\_LOG (  
   mviewowner     IN   VARCHAR2,  
   mviewname      IN   VARCHAR2,  
   mviewsite      IN   VARCHAR2);  
上面两个是一个。     
DBMS\_MVIEW.PURGE\_LOG (  
   master        IN   VARCHAR2,  
   num           IN   BINARY\_INTEGER := 1,  
   flag          IN   VARCHAR2       := 'NOP');  
     
第二个存储过程执行以后，与基表有关的所有的物化视图需要完全刷新；  
第一个存储过程只影响指定的物化视图，这个被影响的物化视图也需要完全刷新；  
具体的用法不是很清楚，欢迎交流。

[Link 1]: http://www.itpub.net/thread-1308625-1-1.html
[baiy.gif]: http://www.itpub.net/static/image/smiley/qqbiaoqin/baiy.gif
[oracle]: http://www.itpub.net/tree/index_1/
[ASE]: http://www.itpub.net/thread-385520-1-1.html
[BI]: http://www.itpub.net/tree/index_339/