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

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: