oracle修改物化视图字段，获取物化视图字段的修改⽮量（⼀）当表建⽴了物化视图⽇志之后，表的DML修改会被记录到物化视图⽇志中，⽽物化视图⽇志则包含了⼀个修改⽮量，来记录哪个列被修改。 在⽂章
列的修改⽮量可以通过2的N次⽅来获得，也就是POWER(2, N)。⽽N的值，就是列的位置。但是如果存在⼀些隐藏的列，通过
DBA_TAB_COLUMNS来获取列的位置就存在问题，⽐如： SQL> CREATE TABLE T_PERSON OF T_PERSON_TYP
2 (ID PRIMARY KEY);
Table created.
SQL> SELECT COLUMN_ID
2 FROM DBA_TAB_COLUMNS
3 WHERE WNER = USER
4 AND TABLE_NAME = 'T_PERSON'
5 AND COLUMN_NAME = 'AGE';
COLUMN_ID
优派viewpad7
----------
3
SQL> SELECT INTCOL#
2 FROM SYS.COL$
3 WHERE NAME = 'AGE'
4 AND OBJ# =尼龙6
5 (SELECT OBJECT_ID
马克思恩格斯著作
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
INTCOL#
----------
5
SQL> CREATE MATERIALIZED VIEW LOG ON T_PERSON;
Materialized view log created.
SQL> INSERT INTO T_PERSON VALUES (1, 'ABC', 18, 'BCD');
1 row created.
1 row updated.
SQL> COL CHANGE_VECTOR$$ FORMAT A30
SQL> SELECT * FROM MLOG$_T_PERSON;
ID SNAPTIME$$ D O CHANGE_VECTOR$$
---------- -------------- - - ------------------------------
1 01-1⽉-00 I N FE
1 01-1⽉-00 U U 20
SQL> SELECT TO_CHAR(POWER(2, 5), 'XX') FROM DUAL;
TO_
---
20
SQL> SELECT TO_CHAR(POWER(2, 3), 'XX') FROM DUAL;
TO_
---
8
很显然，物化视图⽇志中获取的列的偏移量是通过SYS.COL$视图的INTCOL#列获取的，⽽不是通过DBA_TAB_COLUMNS视图。
表SYS.COL$中INTCOL#的值，就是我们要计算的N，得到POWER(2, N)后，通过TO_CHAR转换为16进制的RAW，就是这个列对应的偏移量。
不过还需要注意⼀点，CHANGE_VECTOR$$中的偏移量和直接TO_CHAR得到的结果还是有区别的，⾸先⼆者的排列顺序就有区别： SQL> DROP TABLE T_PERSON PURGE;
Table dropped.
SQL> DROP TYPE T_PERSON_TYP;
帝国主义侵华Type dropped.
SQL> CREATE TABLE T_PERSON
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 ADDR01 VARCHAR2(30),
5 ADDR02 VARCHAR2(30),
6 ADDR03 VARCHAR2(30),
7 ADDR04 VARCHAR2(30),
8 ADDR05 VARCHAR2(30),
9 ADDR06 VARCHAR2(30),
10 ADDR07 VARCHAR2(30),
12 ADDR09 VARCHAR2(30),
13 ADDR10 VARCHAR2(30),
14 ADDR11 VARCHAR2(30),
15 ADDR12 VARCHAR2(30),
16 ADDR13 VARCHAR2(30),
河北理工胡佳
17 ADDR14 VARCHAR2(30),
18 ADDR15 VARCHAR2(30),
19 ADDR16 VARCHAR2(30),
20 ADDR17 VARCHAR2(30),
21 ADDR18 VARCHAR2(30),
22 AGE NUMBER);
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T_PERSON; Materialized view log created.
SQL> SELECT TO_CHAR(POWER(2, INTCOL#), 'XXXXXX')
2 FROM SYS.COL$
3 WHERE NAME = 'AGE'
4 AND OBJ# =
5 (SELECT OBJECT_ID
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
TO_CHAR
-------
200000
SQL> INSERT INTO T_PERSON
2 (ID, NAME, AGE)
3 VALUES (1, 'A', 15);
1 row created.
SQL> UPDATE T_PERSON SET AGE = 18;
1 row updated.
显然物化视图⽇志中的偏移量是逆向的，好在Oracle的内置函数REVERSE也⽀持RAW类型，省得⾃⼰编写这个函数了：SQL> SELECT REVERSE(LTRIM(TO_CHAR(POWER(2, INTCOL#), 'XXXXXX')))
2 FROM SYS.COL$
3 WHERE NAME = 'AGE'
4 AND OBJ# =
5 (SELECT OBJECT_ID
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
REVERSE
-------
000002
这⾥还有问题，REVERSE采⽤字符串的反转⽅法，⽽这时需要RAW类型的反转：
SQL> SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), 'XXXXXX')) AS RAW(255)))
2 FROM SYS.COL$
3 WHERE NAME = 'AGE'
4 AND OBJ# =
5 (SELECT OBJECT_ID
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),'XXXXXX'))ASRAW(255)))
------------------------------------------------------------------
000020
下⾯还有⼀些问题，⾸先就是TO_CHAR后结果的前缀0问题：
嗜血dna
SQL> UPDATE T_PERSON SET ADDR08 = 5;
1 row updated.
1 01-1⽉-00 U U 000400
SQL> SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), 'XXXXXX')) AS RAW(255)))
2 FROM SYS.COL$
3 WHERE NAME = 'ADDR08'
4 AND OBJ# =
5 (SELECT OBJECT_ID
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),'XXXXXX'))ASRAW(255)))
------------------------------------------------------------------
0004
在TO_CHAR的时候使⽤’0X’作为参数可以避免前缀0的问题：
SQL> SELECT REVERSE(CAST(LTRIM(TO_CHAR(POWER(2, INTCOL#), '0XXXXX')) AS RAW(255)))
2 FROM SYS.COL$
3 WHERE NAME = 'ADDR08'
4 AND OBJ# =
5 (SELECT OBJECT_ID
6 FROM DBA_OBJECTS
7 WHERE WNER = USER
8 AND OBJECT_NAME = 'T_PERSON'
9 AND OBJECT_TYPE = 'TABLE');
REVERSE(CAST(LTRIM(TO_CHAR(POWER(2,INTCOL#),'0XXXXX'))ASRAW(255)))
------------------------------------------------------------------
000400
但是另⼀个⿇烦的问题⼜出现了，就是执⾏TO_CHAR的时候需要指定多少个X，X的个数如果少了会报错，个数多了结果⼜不正确。其中X的个数由表中总的列数来决定，具体算法为：
SQL> SELECT FLOOR(LOG(256, POWER(2, COUNT(*)))) + 1
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议