My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.