Geek asks and answers

Identical Oracle db installations: exception on only one of them

edit: Look at the end of this question, what caused the error and how I found out.

I have a very strange exception thrown at me from Hibernate when I run an application that does batch insert data into the oracle database. The error occurs from the Oracle database, ORA-00001 , which

"means that an attempt was made to insert a record with a duplicate (unique) key. This error will also be if the existing record is updated to create a duplicate (unique) key.

The error looks strange, because I created the same table (exactly the same definition) on another computer, where I DO NOT get the same error if I use it through my application. And all the data is inserted into the database, so nothing is rejected.

There should be something different between the two settings, but the only thing I see is the other - this is the output of the banner that I get on release

select * from v$version where banner like 'Oracle%';

Database that causes me problems: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
One that works: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

The table definitions, input, and application I wrote are the same for both. The table uses a four-column table with a composite identifier (serviceid, date, value1, value2) - nothing unusual.

, ? , , , .

:

Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (STATISTICS.PRIMARY_KEY_CONSTRAINT) violated

    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:367)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:8728)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)

,

APC ik_zelf . , Quartz (, ). , , <cronTriggerExpression>0/5 * * * * ?</cronTriggerExpression>, . , , * */1 * * *?. , , , !

1,5-2 , , , , . , 529 , 1000 2000 . crontrigger , , , .

, , true hibernate.cfg.xml .

-- To catch exceptions
-- to find the offending rows run the following query
-- SELECT * FROM uptime_statistics, EXCEPTIONS WHERE MY_TABLE.rowid = EXCEPTIONS.row_id;
create table exceptions(row_id rowid,
                        owner varchar2(30),
                        table_name varchar2(30),
                        constraint varchar2(30));

-- This table was set up
CREATE TABLE MY_TABLE
  (
    LOGDATE DATE NOT NULL,
    SERVICEID           VARCHAR2(255 CHAR) NOT NULL,
    PROP_A   NUMBER(10,0),
    PROP_B NUMBER(10,0),
    CONSTRAINT PK_CONSTRAINT PRIMARY KEY (LOGDATE, SERVICEID)
  );

-- Removed the constraint to see what was inserted twice or more
alter table my_table
  disable constraint PK_CONSTRAINT;

-- Enable this later on to find rows that offend the constraints
alter table my_table
  enable constraint PK_CONSTRAINT
    exceptions into exceptions;
+3
3

. ORA-00001 , , ServiceID, Date, Value1 / Value2. , . , :

  • , ORA-00001
  • , .

: (, ServiceId SYSDATE ). . , , , . , NULL. (NULL, NULL.NULL, NULL), (42, NULL, NULL, NULL).

, , ( , , ). . Bulk DML Exception Handling, PL/SQL. Hibernate : . - , .

. :

alter table t42
    enable constraint t42_uk
        exceptions into my_exceptions
/

, , MY_EXCEPTIONS . , , . , script: $ORACLE_HOME/rdbms/admin/utlexcptn.sql ( , ).

tl; dr

: .

+4

, , EE, SE. , . , SYSDATE, , ; . , ORA-00001.

, .

? .

+1

- . , _ "" , .

, ,

, service_id , . , 1000 ,

SELECT service_id_seq.nextval FROM DUAL

in one database gives a smaller number than another. I see this a lot where the sequence was created (for example, from a control source), and the data was imported into a table from another database.

+1
source

More articles:


All Articles