Our production database locked up once again. Actually it has been happening pretty periodically and the customer is slowly losing patience.
The last time it happened, I ran a query which gave me the following results.

As you can see, the sid=290 is holding row_exclusive locks on the table table_trg_2 and table1.
The table table_trg_2 is being updated in the before insert trigger on table1 just to keep track of who last updated table1.

I really could not understand, how a row-excusive lock could be a blocking lock.
In the above table1, the primary key has a client_id in it. It is extremely unlikely that all the sessions are working wih the same client and thus the same rows. So why is the row-x (sx) lock blocking other dml statements which, most probably are update different rows ?

Also, why does the complete database freeze up because of locks on these tables ?
After the tables start getting locked, even connecting to the instance using sql plus does not work. Sql plus just hangs.

You have to make sure that you have indexes on the FK for the tables involved in the transaction. The explanation is:

"When your application has referential integrity and attempts to modify the child table, Oracle will get a TABLE LEVEL SHARE LOCK on the parent table when there is NO index on the foreign key. To bypass this problme, the most efficient way is to create indexes for all foreign key defined in the database."

what i found was when i update the parent table, oracle obtains a share lock on the child table - and i think this is the lock that is causing the problem.

I understand what you are saying and i checked to see if there was an index on the foreign key, and indeed there was !

But let me give you some more information.
The parent table table_parent has about 15,000 rows.
The child table table_child has about 36,000 rows.
The foreign key is made up of 2 columns - custid and start_date.
What i found was that the start_date cloumn in the table_child, which is part of the foreign key, has mostly null values.
It might be having say only 5000 rows out of the 36,000 with not null values.

Do you think that when oracle is trying to enforce referential integrity, these null values are causing a problem ?

Have you had such an experience before ?
I have had problems in the past with subqueries:
e.g.

select table1_col1 from table 1
where table1_col1 in (select table2_col2 from table2).

Now if table2_col2 is a mix of null and not null values, and the tables are large enough, my query does not return - it just hangs.
I have a feeling that the problem i am facing with the foreighn keys is similar to this situation.

When you say the FK's are indexed - do you mean that there are Foreign Key constraints between table1 and whatever other table (where you do the "auditing")? If that's the case then your trigger is probably actuall a pair of triggers (before and after) and a package to avoid the mutating table problem. The issue of locking the entire child table only applies to the case where a FK constraint exists AND the FK is not indexed (or indexed incorrectly).

Your statement that sqlplus doen not work makes me think that record locking isn't the problem. Does killing the blocking SID (290) resolve the problem? What platform and version DB are you running. Can you monitor machine CPU when the problem occurs?