Problem with logging a Timestamp value.

[madhes]

Hi,

I have a table in Oracle9i, with a timestamp column.
>> create table t1 (ts TIMESTAMP);

I used to enter the data as follows,
>> alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh:mi:ssxff';
>> insert into t1 values (to_timestamp('2002-08-15 10:10:37.123456'));

The format i have set here (yyyy-mm-dd hh:mi:ssxff) is a valid timestamp format in Oracle. This is verified, by selecting the values from the table and it prints the timestamp value in this format (with fractional seconds).

Whenever an operation is performed on Oracle tables, these statements will be written into the Log files (called as REDO Logs) and we can analyze these log files using LogMiner.

When i read the INSERT statement from the LogMiner, its giving me the following statement:
>> insert into "SYSTEM"."T1"("TS&quot values (TO_TIMESTAMP('2002-08-15 10:10:37'));

This statement doesnt contain the fractional part of the second (ie., the last six digits - 123456). Thus, i loss my data, when i read from the log.

Any clues to find whats happening? Or is there any special way exists to get the complete timestamp value?

thanks,
madhes

 

[jeffkish]

I tried this (create table and insert) myself:
create table t1 (ts TIMESTAMP);

alter session set NLS_TIMESTAMP_FORMAT='yyyy-mm-dd hh:mi:ssxff';
insert into t1 values (to_timestamp('2002-08-15 10:10:37.123456'));

and when I select from the table,
the data seems to be there:
TS
2002-08-15 10:10:37.123456

So perhaps the logminer is not doing the 'alter session'?
Or perhaps you are not when you are selecting from it?