AnySQL.net

    在Oracle 10g中的我用两个会话, 搞出了下面这个等待情况, 很是有趣, 也很危险.

SQL> SELECT SID, EVENT FROM V$SESSION
  2  WHERE USERNAME='ANYSQL';

       SID EVENT
---------- -------------------------------------
        29 library cache lock
        32 SQL*Net message from client

    这个情况是如何出来的呢, 在32会话中, 我执行了语句:

SQL> exec dbms_mview.begin_table_reorganization('ANYSQL','T_IOT');

PL/SQL procedure successfully completed.

    在29这个会话中, 我发出如下命令, 但一直处于等待情况:

SQL> delete t_iot;

    看来Oracle的新功能, 有时是比较危险的, 当在第32会话中再发一个Commit之后, 就好了, 29这个会话就可以往下执行了, Oracle为什么不是那个过程最后默认加个Commit呢?

    在Oracle中创建视图时, 如果我们用了"*"符号, 会被自动地根据当时表的定义扩展成字段列表, 在后面再加列时, 新的列不会自动出现在视图的定义中, 直到你重建视图为止. 那么在MVIEW中呢, 通过一个不经意的操作, 发现一个有趣的问题. 总之, 不要随便地在实体视图的定义中使用"*"号.

    下面我们在一个表上建两个实体化视图, 角本如下:

CREATE TABLE T_MVTEST AS SELECT * FROM TAB;
CREATE MATERIALIZED VIEW LOG ON T_MVTEST
    WITH ROWID,SEQUENCE;
CREATE MATERIALIZED VIEW MV_TEST_STAR
    REFRESH FAST WITH ROWID
AS SELECT ROWID RID, A.* FROM T_MVTEST A;
CREATE MATERIALIZED VIEW MV_TEST_NOSTAR
    REFRESH FAST WITH ROWID
AS SELECT ROWID RID, A.TNAME, A.TABTYPE,
   A.CLUSTERID FROM T_MVTEST A;

    接下来对表作一个增加列的操作.

SQL> ALTER TABLE T_MVTEST ADD COL4 VARCHAR2(20);

Table altered.

    接下来来完全刷新两上实体化视图, 看看运行情况:

SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE');
BEGIN DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE'); END;

*
ERROR at line 1:
ORA-12018: following error encountered during code generation for
"ANYSQL"."MV_TEST_STAR"
ORA-00904: "COL4": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_NOSTAR', 'COMPLETE');

PL/SQL procedure successfully completed.

    这是因为在进行全部方式的刷新时, 实体化视图的定义根据当前表的定义被展开了, 将会要刷新新增的"COL4"这个列, 而在表"MV_TEST_STAR"中现在没有这个例, 所以刷新失败, 解决的方法呢是在, 这个视图中手工加一个列:

SQL> ALTER TABLE MV_TEST_STAR ADD COL4 NUMBER;

Table altered.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_TEST_STAR', 'COMPLETE');

PL/SQL procedure successfully completed.

    可以这样解决结果还算好了.

    现在对第一篇中基表进行移动(Move)操作, 会发现不能进行快速刷新, 必须进行全部(Complete)刷新才行. 如下所示:

SQL> ALTER TABLE T_MVLOG MOVE;

Table altered.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST');
BEGIN DBMS_MVIEW.REFRESH('MV_T_MVLOG','FAST'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "ANYSQL"."T_MVLOG" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

    为什么失败, 可以仔细分析一下基于ROWID的MVIEW LOG被刷新的过程, 当基表移动后, ROWID的值变了, 因此不能再继续支持主表的UPDATE/DELETE这样的操作了. 下面将这个实体化视图重新定义成基于主键(Primary Key)的, 首先删除现有的实体化视图及日志, 再为表加一个主键, 重新创建实体化视图 :

SQL>  alter table t_mvlog modify col1 not null;

Table altered.

SQL> alter table t_mvlog add primary key (col1);

Table altered.

SQL> create materialized view log on t_mvlog with primary key, sequence;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T_MVLOG
  2    REFRESH FAST WITH PRIMARY KEY
  3  AS SELECT ROWID R_ID, A.* FROM T_MVLOG A;

Materialized view created.

    再对基表作移动(Move)操作后, 就可以进行增量刷新了.

SQL> alter table t_mvlog move;

Table altered.

SQL> exec dbms_mview.refresh('MV_T_MVLOG','FAST');

PL/SQL procedure successfully completed.

    选择基于ROWID还是基于主键(Primary Key)的实体化视图日志, 还是很重要的.

    最近花了几天时间更深入地研究了一上实体化视图(Materialized View), 接下来就要利用研究的所得去练习一下了, 想做什么呢? 利用实体化视图日志(Materialized View Log)来跟踪Oracle数据库中表的变化, 然后写Perl程序去将变化过的数据刷新到MySQL数据库中, 充分防照实体化视图增量刷新(Fast Refresh)的原理, 可以高效地实现这一功能, 足以应对一般的业务需求了. 下面强调最主要的几点:

1, 每一个表必需要有主键.
2, 用With Primary Key, Sequence选项创建实体化视图日志, 以支持增量刷新.
3, 在实化化视图日志上可以建一个CREATE_DATE列, 默认值SYSDATE.
4, 在实化化视图日志上为SEQUENCE$$列创建一个索引.
5, 现在只考虑简单的记录复制.

    Oracle的增量刷新有些小问题, 当实体化视图日志中有很多的记录时, 刷新过程很容易失败, 因此我在这儿对这个过程作些改进, 根据SEQUENCE$$的值来进行刷新, 每次最多刷新1000条记录, 小批量地做, 可以提高刷新频率.

    程序原型已经出来了, 要保证一次刷新过程的事务一致性, 是个难题, 因为没办法用DB-Link了, 所以正在思考要不要用分布式事务处理的编程. 你有什么建议和想法, 就回复吧!

    使用Online Redefination技术可极少停机时间, 不过也发生过一些小故事. 首先来看一下表能不能支持联机重定义:

SQL>  exec dbms_redefinition.can_redef_table( USER, 'T_DEF1') ;

PL/SQL procedure successfully completed.

    接下来正式开始进行联机重定义:

SQL> exec dbms_redefinition.START_REDEF_TABLE( USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    在将要结束前, 先同步一下变更的记录, 可以常跑这个过程, 以让结束时更快一些.

SQL> exec dbms_redefinition.sync_interim_table( USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    可是现在我在表上面加一个列呢?

SQL> alter table t_def1 add col1000 number;

Table altered.

    现在正式结束联机重定义:

SQL> exec dbms_redefinition.FINISH_REDEF_TABLE(USER, 'T_DEF1', 'T_DEF1_TMP');

PL/SQL procedure successfully completed.

    再来查查表结构?

SQL> desc T_DEF1
Name                   Null?    Type
---------------------- -------- -----------------
OBJECT_ID              NOT NULL NUMBER
......
SECONDARY                       VARCHAR2(1)

SQL> desc T_DEF1_TMP
Name                   Null?    Type
---------------------- -------- ------------------
OBJECT_ID              NOT NULL NUMBER
......
SECONDARY                       VARCHAR2(1)
COL1000                         NUMBER

    只有Temp表上有新增的列, 两个不同的人在同时做这两件事时, 真的很难预先想到这一点.