Oracle 10g Logminer clob bug

最新推荐文章于 2023-02-18 00:39:01 发布

转载最新推荐文章于 2023-02-18 00:39:01 发布 · 317 阅读

文章标签：

本文介绍了如何使用Oracle的闪回技术来查看特定SCN范围内的表更改情况。通过创建表、插入数据并提交，记录下提交前后的SCN值，然后使用DBMS_LOGMNR过程来读取重做日志文件，最终展示出在指定SCN范围内所发生的SQL变更。

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@]

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

转载于:http://blog.itpub.net/19423/viewspace-1043869/