Re: Oracle9i: very big problem with precition of SYSTIMESTAMP !

"Dmitry Bond." <dima_ben_at_ukr.net> wrote in message news: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';
>
>

Log a tar with Metalink.
Jim Received on Thu Sep 16 2004 - 15:53:21 CEST