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';
