Re: problem: unique constraint error reported in alert log and load spike in unix box

This is hardly an answer.

At any rate, we found a similar problem recently on AIX 5.1, Oracle 9.2.0.5. After re-partitioning some tables using dbms_redefinition, a primary key to be created on the new table version took two hours (1M rows), and still wasn't validated. The creating session was waiting on 'direct path read' and consuming huge amounts of CPU. However, a unique index and NOT NULL constraint were created on the same table and column in less than two minutes.

I realize this is not exactly your problem, Zhu, but it sounds like something is amiss in Oracle 9205 land with respect to constraints and validation of such. I would be curious to see what is the session waiting on in your case.

Regards,

Steve Received on Mon Oct 10 2005 - 10:33:31 CDT