NLS_DATE_FORMAT and the Logminer

Recently, I was attempting to replicate some DML that occurred on a table with DATE format columns and I found that the SQL_REDO column was truncating the dates! Obviously, this is not good if you are attempting to replicate the DML with the same date precision.

I will illustrate the problem here, for simplicity’s sake I have reproduced the issue with some very simple representative DML and table structure.

Table Structure:

Let’s assume that we have the following table structure.

SQL> desc kkerekovski.some_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 CREATE_DATE                                        DATE


Original DML:

For this test, we are simply going to insert some random data into the table. It is important to note the value for the DATE formatted column, 2017-04-13 13:52:00. I’ve also gone ahead and checked the SCN value from before and after the insert. We will use these SCN values later on in this post to initialize our logmining sessions.


SQL> set num 38
SQL> select current_scn from v$database;


SQL> insert into kkerekovski.some_table values (1, to_date('2017-04-13 13:52:00','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SQL> commit;

SQL> select current_scn from v$database;


Commit complete.


Initiate the Logmining Session:

Here is an example of the script that I will use to initiate the logmining session. It takes two arguments, the startscn and endscn which I have already captured.

SQL> !cat logmine.sql
set echo on;
sys.dbms_logmnr.start_logmnr (
startscn => &1,
endscn => &2,

Now we can call this script and pass the appropriate SCN values to initiate the logmining session.

SQL> @logmine.sql 16583813 16583878
  2  sys.dbms_logmnr.start_logmnr (
  3  startscn => &1,
  4  endscn => &2,
  9  );
 10  END;
 11  /
old   3: startscn => &1,
new   3: startscn => 16583813,
old   4: endscn => &2,
new   4: endscn => 16583878,

PL/SQL procedure successfully completed.


Reproduce the issue:

Here we can clearly see that the v$logmnr_contents.sql_redo column is giving us less than ideal data. The reconstucted SQL statement is using the date format DD-MON-RR which leaves out the hourly, minutely and by second precision which was used in the original insert statement.

SQL> col sql_redo format a200
SQL> select sql_redo from v$logmnr_contents where seg_owner = 'KKEREKOVSKI' and seg_name='SOME_TABLE';

insert into "KKEREKOVSKI"."SOME_TABLE"("ID","CREATE_DATE") values ('1',TO_DATE('13-APR-17', 'DD-MON-RR'));


Why is this happening?

The reason that this problem is occurring is because v$logmnr_contents attempts format the restructured sql statement according to the session’s NLS parameters. In this case, we are being hampered by the default NLS_DATE_FORMAT


The fix:

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select sql_redo from v$logmnr_contents where seg_owner = 'KKEREKOVSKI' and seg_name='SOME_TABLE';

insert into "KKEREKOVSKI"."SOME_TABLE"("ID","CREATE_DATE") values ('1',TO_DATE('2017-04-13 13:52:00', 'YYYY-MM-DD HH24:MI:SS'));


The above sqlplus output clearly shows that by altering the NLS_DATE_FORMAT session parameter, we can influence the format of any TO_DATE functions returned by V$LOGMNR_CONTENTS.SQL_REDO.

I think that the big lesson to be learned here, is that if you are going to be using v$logmnr_contents to reconstruct SQL statements that involve the use of any TO_DATE or similar functions that require some sort of DATE masking, it would be a good idea to set the defaults for your session to an appropriate value.





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s