Deadlock detected while inserting into Oracle table

fredy · Post by **fredy** » Mon Sep 01, 2008 12:07 am

Hi,

I have 9 million records in my source table. When i tried to load all the records then i got the following error:
Array execute failed for insert:
qlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource

When i filter for 50 records in the source then the job runs successfully.

I have given the insert mode as UPSERT. In that update is a dummy query (Where 1=2)

Your help is highly appreciated.

Thanks in advance.

umamahes · Post by **umamahes** » Mon Sep 01, 2008 12:29 am

Is there are any bitmap indexes enabled on that table.If you haveany bitpam indexes please make unusable before loading.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 01, 2008 12:32 am

Same table as source?

fredy · Post by **fredy** » Mon Sep 01, 2008 12:40 am

Thanks for your replies.

Target table is different from source table. No index on target table.

But one stored procedure (which will refer the target stage) is used before the target stage

jenny_wang · Post by **jenny_wang** » Mon Sep 01, 2008 2:32 am

try to use sequential mode when insert.

fredy · Post by **fredy** » Mon Sep 01, 2008 9:00 am

Yes. I have tried it. But still getting the same deadlock error

gabrielac · Post by **gabrielac** » Tue Sep 02, 2008 6:07 am

- Have you tried using the Load option instead of insert?
- Have you changed the upsert time for commit / rows for commit environment variables?

Gaby