Oracle9i: very big problem with precition of SYSTIMESTAMP !

[Dmitry Bond.]

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_C0020 2884) 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.247000 000
2004-09-16-10.12.16.263000 000
2004-09-16-10.12.16.278000 000
[...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_F ORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF';

[Jim Kennedy]

"Dmitry Bond." <di******@ukr.n et> wrote in message
news:10******** *******@moxa.un ited.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_C0020 2884) 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.247000 000
2004-09-16-10.12.16.263000 000
2004-09-16-10.12.16.278000 000
[...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_F ORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF';

Log a tar with Metalink.
Jim