Logmnr分析SQL长度大于8000的潜在问题.

问题的起因是被同事问起当SQL_UNDO长度大于4000是怎么组装SQL。

我们都知道通过LOGMINER可以找出相应的REDO SQL和UNDO SQL，经过DBMS_LOGMNR分析后，相应的REDO SQL和UNDO SQL存在视图V$LOGMNR_CONTENTS,从这张视图的结构里可以看出，字段sql_redo,sql_undo的类型为varchar2(4000),从这里可以看出v$logmnr_contents里的一行记录里，存储的SQL长度至多为4000,但有时SQL的长度超过4000怎么办？

[@more@]从reference文档里可以看到这张视图还有几个字段：RS_ID,SSN,CSF.他们的作用是：
RS_ID: Record set ID. The tuple (RS_ID, SSN) together uniquely identifies a row in

v$LOGMNR_CONTENTS. RS_ID uniquely identifies the redo record that generated the row.
SSN: SQL sequence number. Used in conjunction with RS_ID, this uniquely identifies a row in

the V$LOGMNR_CONTENTS view.
CSF: Continuation SQL flag. Possible values are:
0 = indicates SQL_REDO and SQL_UNDO is contained within the same row
1 = indicates that either SQL_REDO or SQL_UNDO is greater than 4000 bytes in size and is continued in the next row returned by the view

从这里可以看出RS_ID,SSN组合一起，可以唯一识别v$logmnr_contents的一条记录，而CSF为1时，表示当前的sql_redo/sql_undo是不完整的，要跟v$logmnr_contents的后面记录组合起来，这样问题就来了，如果一条很长的SQL，导致v$logmnr_contents的cfs=1时，有多条记录时，如何保证这些SQL能够正确的组装成一个完整的SQL呢？如果要组装的SQL长度小于8000，这个是没问题的，通过RS_ID,SSN把关联的记录选出来，然后通过CFS来区分，如果SQL长度大于8000,就上面的3个字段是不能保证的。根据CSF后面那句话“continued in the next row returned by the view”，紧接着后面那条记录可以跟上面的记录合并，怎么能确保顺序正确的？是不是v$logmnr_contents这视图本身定义了内部之间的顺序？

执行下面两条SQL来确认：

select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$LOGMNR_CONTENTS';
select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$LOGMNR_CONTENTS';

从结果来看，v$logmnr_contents的基表是x$logmnr_contents，在视图的定义里没有定义里面的内在顺序，导致的一个担心就是：多条记录的RS_ID,SSN,CSF(值为1)都相同时，不能保证我拼接起来的SQL是否正确。特别当写程序去拼装这些SQL时，不能百分百的保证组装起来的SQL是正确的。

就这个问题开了个SR，从Oracle那里得到确认，这是一个bug5852363,但还在开发中。

下面的测试，是证实RS_ID,SSN,CSF相同的多条记录.本身测试一下9i的版本，但找不到9i的了。
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> !uname -a
Linux hostname 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU/Linux

SQL> create table test99(id int,a varchar2(3000),b varchar2(3000),c varchar2(3000),d

varchar2(3000));

Table created.

SQL> insert into test99 values(1,rpad('a',3000,'a'),rpad('b',3000,'b'),rpad

('c',3000,'c'),rpad('d',3000,'d'));

1 row created.

SQL> insert into test99 values(2,rpad('a',3000,'a'),rpad('b',3000,'b'),rpad

('c',3000,'c'),rpad('d',3000,'d'));

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select group# from v$logfile where status='CURRENT';

no rows selected

SQL> select member from v$logfile where group# in (select group# from v$log where

status='CURRENT');

MEMBER
--------------------------------------------------------------------------------------------

---------------------------------------- --------------------------

----------------
/oracle/oradata/test/redo4.log

SQL> delete from test99 where id=1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_logmnr.add_logfile('/oracle/oradata/test/redo4.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select rs_id,ssn,csf,substr(sql_undo,1,10) from v$logmnr_contents where

seg_name='TEST99';

RS_ID SSN CSF SUBSTR(SQL
-------------------------------- ---------- ---------- ----------
0x002dfd.00000043.0010 0 1 insert int
0x002dfd.00000043.0010 0 1 bbbbbbbbbb
0x002dfd.00000043.0010 0 1 cccccccccc
0x002dfd.00000043.0010 0 0 dddddddddd

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL> alter system switch logfile;

System altered.

SQL> select member from v$logfile where group# in (select group# from v$log where

status='CURRENT');

MEMBER
--------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------

----------------
/oracle/oradata/test/redo5.log

SQL> update test99 set a=rpad('A',3000,'A'),b=rpad('B',3000,'B'),c=rpad('C',3000,'C'),d=rpad

('D',3000,'D') where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> exec dbms_logmnr.add_logfile('/oracle/oradata/test/redo5.log',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

SQL> select rs_id,ssn,csf,substr(sql_undo,1,10) from v$logmnr_contents where

seg_name='TEST99';

RS_ID SSN CSF SUBSTR(SQL
-------------------------------- ---------- ---------- ----------
0x002dfe.0000009f.0010 0 1 update "SY
0x002dfe.0000009f.0010 0 1 bbbbbbbbbb
0x002dfe.0000009f.0010 0 1 cccccccccc
0x002dfe.0000009f.0010 0 1 dddddddddd
0x002dfe.0000009f.0010 0 1 BBBBBBBBBB
0x002dfe.0000009f.0010 0 1 CCCCCCCCCC
0x002dfe.0000009f.0010 0 0 DDDDDDDDDD

7 rows selected.

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

转载于:http://blog.itpub.net/45188/viewspace-1027484/