Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
enq: TM - contention

Hello Everyone,
Just One day before one Developer told me that my application hangs too much. So when it hangs next time I queried v$session and in the event I found 'enq: TM - contention'. So I google this event and I found that there is a locking issue of table. So making an index in the foreign key column will solve your problem and problem is solved.
But I want to know How index comes into the picture ? What index actually doing that cause my issue to be resolved.
Please help me out to understand this.
Answers
-
Which version you are in. It happens with foreign key in 11g.
-
Hi,
For know what FK is not indexed, run the query bellow:
SELECT * 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.constraint_type = 'R'
AND c.owner like 'owner'
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
AND i.owner like 'owner'
)
ORDER BY owner,table_name, column_position;
Hope this help you.
Regards
-
HI please follow this doc
1343365.1
-
when the table is empty this happens too
so don't just skip the empty tables ... also add an index on those tables showing the enq lock
you should be getting an TM03 in P1 for this
-
Hi,
Try to get the 'p2' parameter of the enqueue wait event. It's the object_id of the locked object.
If you have diagnostic pack, following blocking sessions in ASH may help.
Look at foreign keys where parent is deleted (of referenced column udpated) and you have no index starting with the foreign key columns. The locking follows the cascade (delete or setnull - even when the set null is not on referenced column)
Non indexed foreign key is the most frequent reason for TM Share lock, but you should check that there is no concurrent DDL occurring (create index for example)
Regards,
Franck.