Oracle8 and constraints

[jensm]

Hello...

This is our problem:

A few days ago our Oracle database crashed completely.
It took us several days to repair it. We exported the whole
database and then we imported it into a new database.

Now we have the problem that we get a "Deadlock" when a user tries to change data in table "1" while an other user is changing data in table "2". The only relationship between these two tables is a foreign key.
(The foreign keys where created with "deferrable initially immediate&quot.

This problem did not occur bevor the crash. We did not change anything about the database and we did not change the programs which access the database.

Who knows the solution?

 

[lfoata]

I would try to drop and recreate the constaint and see if any errors come up.
do not have a solution, could be a lot of things.

 

[rcurva]

This is a common one, to get rid of this, create an index to the column of the child table (FK column).

If this doesn't solve the problem investigate further
if there are traces generated in your udump.

 

[jcote]

First of all, I think in a situation where you have a problem with the server on which the database is, you should not use an IMPORT to recreate the database. Instead, in your backup strategy, you should include either a cold or hot backup of the underlying files that creates the database (datafiles, redo logs and control files). When a problem happens, you restore all the files, and you are shure that you have all the objects that were in your database at the time of the backup. With the IMPORT utility, I have seen some situations where not all of the objects were restored.

I agree with rcurva. The situation you describe seems to be related with missing indexes on the child table (foreign key). I would guess that the import did not re-create all of the indexes. Look at the import log of the full import you did to check if this is the case.

I emphasize again on the fact that EXP and IMP should be used to restore tables in situation where a table was accidently dropped of the data in it was badly altered. In a instance failure, restore of the datafiles should be used instead.