Re: Duplicate timestamp(6): How is this possible?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Wed, 19 Jan 2011 15:01:04 -0800
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:
