Oracle9i: very big problem with precition of SYSTIMESTAMP !

From: Dmitry Bond. <dima_ben_at_ukr.net>
Date: Thu, 16 Sep 2004 13:25:51 +0300
Message-ID: <1095330352.358741_at_moxa.united.net.ua>



Hello All.

Could you please help us with one issue?

We have a table with QTIME TIMESTAMP(9) field and this field is primary key in the table.
When we run 400 (or more) insert SQLs like the:

  insert into Table1 (qtime, v1, v2) values (systimestamp, 'Vnnnnn', 'Vnnnnn');

we got 150 (or more) error messages that says:

  ERROR at line 1:
  ORA-00001: unique constraint (TEST.SYS_C00202884) violated

Finally only 230-270 rows were inserted successfully (rest or rows rejected with the mentioned error message).
The SQL:

  select qtime from Table1

shows something like the:

QTIME


2004-09-16-10.12.16.247000000
2004-09-16-10.12.16.263000000
2004-09-16-10.12.16.278000000

[...etc...]

As you can see only first 3 digits are different in QTIME values. As I got the problem occurs because of bad precision of SYSTIMESTAMP. I think that insert SQLs executed faster than values returned by SYSTIMESTAMP became different...

We want to have at least 6 unique digits in seconds fraction! Is it possible to achieve this with Oracle ?

Oracle documentation said: "The exact resolution depends on the operating system clock.".
But we also experienced in working with IBM DB2 - it provides 6 unique digits for seconds fraction (in the similar hardware and software configuration), then we have two options: 1) it is the BUG in Oracle9i;
2) or we need to configure something in Oracle to achieve needed precision of SYSTIMESTAMP.
I hope this is not BUG of Oracle...
I hope we can configure something to resolve this problem. But the question - what?...

Could you please share some your experience concerning the case? Could you please provide us with some advices ?

WBR,
Dmitry.

ps. our Oracle server has the following configuration:   CPU=Dual AMD Athlon 2000+ MP, RAM=2Gb, HDD=80Gb   OS=Windows Server 2003 Standard with all latest hotfixes from MS

pps. NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF'; Received on Thu Sep 16 2004 - 12:25:51 CEST

Original text of this message