问题的起因是被同事问起当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/