enq: TM - contention

The waits on the enq: TM (Table modification) – contention event for the sessions that are waiting to perform insert operations are almost always due to an unindexed foreign key constraint.. This happens when a dependent or child table’s foreign key constraint that references a parent table is missing an index on the associated key. 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. Note that these are full table locks (TM), and not row-level locks (TX)—thus, these locks aren’t restricted to a row but to the entire table.

You can find unindex foreign key constraints with blew script:

SELECT tbl.*,
       'CREATE INDEX IDXFK_' || substr(tbl.column_name, 1, 23) || ' ON ' ||
       tbl.table_name || ' ( ' || tbl.column_name ||
       ' ) tablespace main_index ; '

  FROM (SELECT c.owner,
               c.table_name,
               cc.column_name,
               cc.position column_position
      
          FROM DBA_constraints c, DBA_cons_columns cc
         WHERE c.constraint_name = cc.constraint_name
           and c.OWNER = 'Yuor Username'
           AND c.constraint_type = 'R'
           and c.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
          FROM DBA_indexes i, DBA_ind_columns ic
         WHERE i.index_name = ic.index_name) tbl
 ORDER BY table_name;