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

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • Date: Wed, 19 Jan 2011 15:43:44 -0800

I believe I may have figured this out. I am using current_timestamp. I
believe that for a given moment in time, this value could be different
depending on the location of the user. 'believe' because I'm not really
sure. In anycase, I think that our India team as started entering
transactions that formerly only came from the US of A.

I think that if I change the default to SYSTIMESTAMP, the local server
timestamp will be populated.

If anybody can confirm my reasoning, it would be helpful.

Thanks everybody for trying to help me work this out.

Mike



On Wed, Jan 19, 2011 at 3:01 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:

> Not stupid at all. I talked with our server folks about this and they said
> that the server time can 'drift'. I don't know if drift can cause an overlap
> in time or if it's more like a rubber band effect. In any case, they
> reviewed their log and say that there were no clock anomolies
> Mike.
>
>
> On Wed, Jan 19, 2011 at 2:51 PM, Stephane Faroult 
> <sfaroult@xxxxxxxxxxxx>wrote:
>
>>  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>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>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> wrote:
>>>>
>>>>> how is the code called?
>>>>>
>>>>> On Wed, Jan 19, 2011 at 9:30 PM, Michael Moore <
>>>>> 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: