RE: Duplicate timestamp(6): How is this possible?
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <niall.litchfield@xxxxxxxxx>, <michaeljmoore@xxxxxxxxx>
- Date: Wed, 19 Jan 2011 17:10:55 -0500
Two transactions can be inserted at the same time, even if they were
initiated at slightly different times, due to various waits. Also depending
on the os, the granularity of the clock might make it so that rounding
causes the same time to be inserted. Some os's only go to 3 decimal places,
others to 6, regardless of what the datatype is.
Ken
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Niall Litchfield
Sent: Wednesday, January 19, 2011 4:46 PM
To: michaeljmoore@xxxxxxxxx
Cc: oracle_l
Subject: Re: Duplicate timestamp(6): How is this possible?
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:
