Oracle Database Discussions

Hi,

I have a problem with deadlocks and unique constraint violations. My database has several tablespaces associated with multiple datafiles. The table needs to support a high load of inserts. It needs to store the data from the last 30 days. Therefor I partitioned that table using list partitions, each for every possible day of a month (31 partitions). A job is scheduled to clean up a partition of the next day. The table has a primary key of 2 columns: LogId and TimeStamp. The database has a system tablespace of 150M, the data and indexes of the 31 partitions are distributed over 8 tablespaces of 200M and 8 index tablespaces . The tablespaces are defined like this:

DROP TABLESPACE DATA01 INCLUDING CONTENTS;
CREATE TABLESPACE DATA01 DATAFILE
'C:\oracle\oradata\CTT\CTT_DATA01_01.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_02.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_03.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_04.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_05.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_06.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_07.dbf' SIZE 20M AUTOEXTEND OFF,
'C:\oracle\oradata\CTT\CTT_DATA01_08.dbf' SIZE 20M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8k
SEGMENT SPACE MANAGEMENT AUTO;

I made sure that INITRANS on the table is higher than the number of threads inserting. I also set FREELISTS to 3 on my indexes and primary key. The database runs on my windows 2000 development machine. The production environment will be a unix box.

The client of this database is a Java application. For this test case it runs on the same machine as the database. It processes log files from another application, parses them and then stores the data into the Oracle 9.2 database. The storer part of the application is configured to use 3 threads (can be set to whatever value) to insert the data into 1 specific table, using batches of prepared statements.

I prepared a test file with around 10000 different records. When I run the Java application, things go quite well for the first 2000 records, but then I get strange results from Oracle:
- When I leave the primary key on the table, Oracle tells me there are unique constraint violations. The application cannot process any other records and keeps sending me the unique constraint violation. The tracefile in the udump folder tells me there is a deadlock.
- When I configure the application to use only one thread, I can see one active session, but I still get the unique constraint violated exception.
- When I remove the primary key from the table, but do multithreaded inserts, I still get a deadlock.

Does anyone recognize a symptom of what I can be doing wrong here?
Is any of my tablespaces too small? Is my pc too slow to process batch inserts of 3 threads at the same time?

Thanks in advance!
Peter