Re: Duplicate timestamp(6): How is this possible?
- From: Fernando José Andrade <jotawolf@xxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx
- Date: Thu, 20 Jan 2011 08:54:20 +0100
For sure that's the problem.
*alter session set TIME_ZONE = 'Europe/Madrid';*
alter session set correcto.
*select current_timestamp
from dual;*
CURRENT_TIMESTAMP
-----------------
20/01/11 08:52:48,680946000 EUROPE/MADRID
*select systimestamp
from dual;*
SYSTIMESTAMP
-------------
20/01/11 08:52:48,683776000 +01:00
alter session set TIME_ZONE = 'America/New_York';
alter session set correcto.
*select current_timestamp
from dual;*
CURRENT_TIMESTAMP
-----------------
20/01/11 02:52:48,691017000 AMERICA/NEW_YORK
*select systimestamp
from dual;*
SYSTIMESTAMP
-------------
20/01/11 08:52:48,693412000 +01:00
On Thu, Jan 20, 2011 at 12:43 AM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
--
---------------------------------------
Fernando Jose Andrade
http://www.fjandrade.com
M: +0034-649-162-100
@Madrid.Spain
---------------------------------------
Other related posts:
