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 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table tbl_clob(id number,fld_clob clob,primary key(id));

表已建立。

SQL> select * from v$log where status='CURRENT';

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- --------------
3 1 12 52428800 1 NO CURRENT
216826 29-12月-10

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
231724

SQL> insert into tbl_clob values (1,'''');

已建立 1 行。

SQL> commit;

提交完成。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
231727

SQL> select * from v$log where status='CURRENT';

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- --------------
3 1 12 52428800 1 NO CURRENT
216826 29-12月-10

SQL> select * from v$logfile where group#=3
2 ;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------

IS_
---
3 ONLINE
C:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG
NO

SQL> begin
2 sys.dbms_logmnr.add_logfile('C:ORACLEPRODUCT10.2.0ORADATATESTREDO03
.LOG');
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> begin
2 sys.dbms_logmnr.start_logmnr(startScn => 231724,endScn => 231727,Options
=> 2064);
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> select sql_redo from v$logmnr_contents;

SQL_REDO
--------------------------------------------------------------------------------

set transaction read write;
insert into "TEST"."TBL_CLOB"("ID","FLD_CLOB") values ('1',EMPTY_CLOB());
update "TEST"."TBL_CLOB" set "FLD_CLOB" = ''' where "ID" = '1';
commit;

挖掘出來的update是無法直接執行的，Oracle 忽略了輸入單引號的情況。[@more@]

Oracle 10g Logminer clob bug

相關文章