ORA-00001 : Unique Constraint Violated caused by DATE VS Timestamp(6)

Create table tt1 (
dt date primary key
);
Create table tt2(
dt timestamp(6) primary key
);
Insert into tt2 values('25-NOV-13 04.18.47.138746000 PM');
Insert into tt2 values('25-NOV-13 04.18.47.66 PM');
commit;

Insert into tt1 select * from tt2;

ORA-00001: unique constraint violated 
unique constraint (%s.%s) violated"

This is because Oracle does not store fractional part for DATE data type. These 2 rows are considered duplicate in TT1. This issue could happen in data migration project if you had not noticed the difference between date and timestamp(6) data type.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061070/，如需轉載，請註明出處，否則將追究法律責任。