Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. And these locks on the child table are full table locks (TM) i.e. these locks aren’t restricted to any row but to the entire table.

Now the problem occurs when other sessions try to modify this child table they see a full table lock on the child table and thus have to wait for the lock to be released. This wait is denoted as enq: TM contention.

Now to resolve these waits we have to ensure that those full table locks are avoided and the easiest way to do it is by indexing the unindexed foreign key constraint

Using below query you can find all unindexed foreign key constraints in a specific schema:-

select * from (
select ct.table_name, co.column_name, co.position column_position
from user_constraints ct, user_cons_columns co
where ct.constraint_name = co.constraint_name
and ct.constraint_type = 'R'
minus
select ui.table_name, uic.column_name, uic.column_position
from user_indexes ui, user_ind_columns uic
where ui.index_name = uic.index_name
)
order by table_name, column_position;