Re: Duplicate timestamp(6): How is this possible?

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: michaeljmoore@xxxxxxxxx
  • Date: Wed, 19 Jan 2011 23:51:09 +0100

This is possibly a stupid idea because I have no idea how it works
exactly, but is the clock of your server synchronized with a time
server? Suppose that your computer's clock is just a little too fast and
is adjusted - you might get the same timestamp twice.

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

On 01/19/2011 11:22 PM, Michael Moore wrote:
> Additional info: This has been working for months with millions of
> transactions with no problems.
>
> Mike
>
>
> On Wed, Jan 19, 2011 at 2:20 PM, Michael Moore
> <michaeljmoore@xxxxxxxxx <mailto:michaeljmoore@xxxxxxxxx>> wrote:
>
>     This might be helpful. it is a small sample of data from the
>     table. As you can see, the timestamp does not appear to be
>     truncated as the result of an OS precision limitation.
>
>
>     1/19/2011 7:57:41.014856 AM    1023206804    p_ordernumber_i
>     =1023206804 p_calling_program_i =PBILLING2
>     p_leadreturnstatus2tcode_i =34650    1/18/2011 11:57:13 PM
>
>     1/19/2011 7:57:41.120702 AM    1023206804    a2#
>     v_commissionable_type=DoNotPay    1/18/2011 11:57:13 PM
>     1/19/2011 7:57:53.108784 AM    665693897    p_ordernumber_i
>     =665693897 p_calling_program_i =PBILLING2
>     p_leadreturnstatus2tcode_i =34650    1/18/2011 11:57:13 PM
>
>     1/19/2011 7:57:53.193272 AM    665693897    ORD# 665693897
>     ORA-20993: Either TAFFILIATE or TACCOUNTPROGRAM was
>     affil_not_allowed_returns_yn = Y.    1/18/2011 11:57:13 PM
>
>     p_leadreturnstatus2tcode_i =34650    1/18/2011 11:57:13 PM
>
>
>     On Wed, Jan 19, 2011 at 2:11 PM, Michael Moore
>     <michaeljmoore@xxxxxxxxx <mailto:michaeljmoore@xxxxxxxxx>> wrote:
>
>         The code is a procedure which exists only in the package body
>         (i.e. no api in the spec for this procedure).
>
>         v_read_cnt and v_run_date are global package variables.
>
>         Sample call:
>
>         log_audit_info ('p1# STARTING get_commissionable_type');
>
>         thanks,
>         Mike
>
>
>
>         On Wed, Jan 19, 2011 at 1:45 PM, Niall Litchfield
>         <niall.litchfield@xxxxxxxxx
>         <mailto:niall.litchfield@xxxxxxxxx>> wrote:
>
>             how is the code called?
>
>             On Wed, Jan 19, 2011 at 9:30 PM, Michael Moore
>             <michaeljmoore@xxxxxxxxx <mailto:michaeljmoore@xxxxxxxxx>>
>             wrote:
>
>                 The problem is duplicate on primary key which is
>                 timestamp(6);
>
>                 CREATE TABLE TCOM_RETURN_ADJ_AUDIT
>                 (
>                   TIME_D         TIMESTAMP(6)                  
>                 DEFAULT current_timestamp         NULL,
>                   RECORD_NUMBER  NUMBER(10)                     NOT NULL,
>                   MSG            VARCHAR2(4000 BYTE)                NULL,
>                   RUN_DATE       DATE                               NULL
>                 );
>
>                 CREATE UNIQUE INDEX TCOM_RETURN_ADJ_AUDIT_PK ON
>                 TCOM_RETURN_ADJ_AUDIT
>                 (TIME_D)
>                 LOGGING
>                 TABLESPACE COM_INDX_DS
>                 NOPARALLEL;
>
>                 ALTER TABLE TCOM_RETURN_ADJ_AUDIT ADD (
>                   CONSTRAINT TCOM_RETURN_ADJ_AUDIT_PK
>                   PRIMARY KEY
>                   (TIME_D)
>                   USING INDEX TCOM_RETURN_ADJ_AUDIT_PK);
>
>
>                 Program does not insert the TIME_D value. Program does
>                 not provide TIME_D; actual value is obtained from
>                 table definition which has DEFAULT current_timestamp
>                 (see above);
>
>                 The code that does the insert:
>
>                    PROCEDURE log_audit_info (p_msg_i IN VARCHAR2)
>                    IS
>                       PRAGMA AUTONOMOUS_TRANSACTION;
>                    BEGIN
>                       INSERT INTO tcom_return_adj_audit
>                 (record_number, msg, run_date)
>                            VALUES (v_read_cnt, p_msg_i, v_run_date);
>
>                       COMMIT;
>                    END log_audit_info;
>
>
>                 THE ERROR
>
>                 ORA-00001: unique constraint
>                 (COM_APP.TCOM_RETURN_ADJ_AUDIT_PK) violated
>
>
>
>                 The server clock was not changed, already looked into
>                 this as a possibility.
>
>                 Thanks,
>
>                 Mike
>
>
>
>
>             -- 
>             Niall Litchfield
>             Oracle DBA
>             http://www.orawin.info
>
>
>
>

Other related posts: