Oracle9i: Problem with logging a Timestamp value.

[madhes]

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.

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. (Its a kind of utility supported by Oracle.)

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?

--madhes

 

[Mockenrue]

You might post your question in the "Oracle: Oracle release 9i" forum.

 

[karluk]

I agree that this question should be posted in the Oracle 9i forum. However, I would have to say that your symptoms strongly suggest that your nls_timestamp_format has not been set to accept fractions of seconds, despite what you attempted to do with your "alter session" command. Have you checked nls_session parameters to find out what the actual format is set to prior to the insert?

 

[madhes]

Thanx. Yep! I agree that i should have posted this in 9i forum. (I just did that).

And regarding the format i ve given here, is a valid format and after setting this format, the NLS_SESSION_PARAMETER contains this format and when I select the values from the table, it prints the timestamp values in this format (with the fractional part of the second). But when i read from the Log, its not giving the fractional part!
-madhes