Logminer Shows Zero In The Millisecond Section Of TIMESTAMP Datatype Column (Doc ID 1335358.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.0.1.3 to 10.2.0.4 - Release: 9.0.1 to 10.2
Information in this document applies to any platform.

Symptoms

When extracting the information about DML from the redo logs using Logminer, you will find that the millisecond section of the TIMESTAMP datatype is zero.

For example, your initial TIMESTAMP values in the column are:

SQL> select * from DC_TEST;

NUM              TS
---------------- -------------------------------------------------
               1 05-JUL-11 09.31.02.494840 AM
               2 05-JUL-11 09.31.02.543325 AM

But when you extract the Logminer values, you see the miliseconds are zeroed, like:

select sql_redo from v$logmnr_contents where table_name='DC_TEST';

SQL_REDO
----------------------------------------------------------------------------
insert into "TEST"."DC_TEST"("NUM","TS") values ('1',TO_TIMESTAMP('05-JUL-11 09.31.02.000000 AM'));
insert into "TEST"."DC_TEST"("NUM","TS") values ('2',TO_TIMESTAMP('05-JUL-11 09.31.02.000000 AM'));

Changes

The next test case demonstrates this:

-- enable the supplemental logging
connect / as sysdba
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
YES

-- create a test user and switch the log file
create user test identified by test;
grant dba to test;
alter system switch logfile;

-- create a table and add some values
connect test/test
create table DC_TEST (NUM number , TS TIMESTAMP);
insert into DC_TEST values (1, systimestamp);
insert into DC_TEST values (2, systimestamp);
select * from test.DC_TEST;

NUM         TS
----------- --------------------------------------------------------------
          1 05-JUL-11 09.31.02.494840 AM
          2 05-JUL-11 09.31.02.543325 AM

-- check the current redo log / name
select * from v$log;
select * from v$logfile;

-- add logfile / start logging the redo
EXEC DBMS_LOGMNR.ADD_LOGFILE (LOGFILENAME => '/app/oracle/oradata/S112W6/redo02.log',
                              OPTIONS     => dbms_logmnr.ADDFILE);

EXEC DBMS_LOGMNR.START_LOGMNR (OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

-- read from the v$logminer_contents

COLUMN SQL_UNDO FORMAT a75 HEADING 'SQL UNDO ' wrap
COLUMN SQL_REDO FORMAT a75 HEADING 'SQL REDO' wrap
select SQL_REDO fROM V$LOGMNR_CONTENTS WHERE table_name = 'DC_TEST';

SQL REDO
---------------------------------------------------------------------------
create table DC_TEST ( NUM number , TS TIMESTAMP , TSTZ TIMESTAMP ,TSLTZ TI
MESTAMP );

insert into "TEST"."DC_TEST"("NUM","TS") values ('1',TO_TIMESTAMP('05-JUL-11 09.31.02.000000 AM'));

insert into "TEST"."DC_TEST"("NUM","TS") values ('2',TO_TIMESTAMP('05-JUL-11 09.31.02.000000 AM'));

-- end the logminer session
EXEC DBMS_LOGMNR.END_LOGMNR();

Cause

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