LogMiner Returns Wrong Delete SQL_REDO Statement For BLOB Datatype (Doc ID 2179380.1)

Last updated on OCTOBER 31, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2.0 and later
Information in this document applies to any platform.

Goal

LogMiner returns wrong sql_redo for BLOB data type as shown below:

CREATE TABLE "LOGDEVICE"
(
"DEVICEOBJECT" BLOB,
"ID" NUMBER(*,0),
PRIMARY KEY ("ID")
)
LOB ("DEVICEOBJECT") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
);

SQL> insert into LOGDEVICE values ('aaa',1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from LOGDEVICE where id =1;

1 row deleted.

SQL> commit;

By mining the corresponding redo log the output from v$logmnr_contents shows the following statement for the DELETE sql executed above:( NOTE: the "and" in the where clause):

delete from "QATUSER"."LOGDEVICE" where and "ID" = '1'; >>>>>> we can see "and"immediately after where without a condition.

The correct sql_redo should look like below:

delete from "SCOTT"."LOGDEVICE" where "ID" = '1' and ROWID = 'AAATu8AABAAAZhZAAA';

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms