Monday, August 14, 2017

A Different Cause for enq: TM - contention

Doc ID 1905174.1 on My Oracle Support looks at how to resolve this wait event.

It says:

If contention is occurring, then the most common reason is missing Foreign Key (FK) index on the FK constraint columns in the Child tables.

In my experience until very recently, this has always been true. However, I have now found another possible cause, which I have recreated below in an Oracle database:

First I created a couple of users called ANDREW and FRED:

SQL> create user andrew
  2  identified by reid
  3  default tablespace users
  4  quota unlimited on users
  5  /

User created.

SQL> grant create session, create table to andrew
  2  /

Grant succeeded.

SQL> create user fred identified by bloggs
  2  /

User created.

SQL> grant create session to fred
  2  /

Grant succeeded.


I logged in as ANDREW, created a table, allowed FRED to update it and did an update on it myself. This created a lock as I did not commit the update. Note that the table had no foreign key constraints:

SQL> conn andrew/reid
SQL> create table tab1 as select 1 col1 from dual
  2  /

Table created.

SQL> grant update on tab1 to fred
  2  /

Grant succeeded.

SQL> update tab1 set col1 = 2
  2  /

1 row updated.


I then logged in as user FRED in a second SQL*Plus session and tried to lock the table but this did not work:

SQL> conn fred/bloggs


SQL> lock table andrew.tab1 in exclusive mode
  2  /

I waited a few minutes before logging in as SYS in a third SQL*Plus session and found that FRED was waiting on the enq: TM – contention event:

SQL> conn / as sysdba


SQL> select event, time_waited/100
  2  from v$session_event
  3  where sid =
  4  (select sid from v$session
  5   where username = 'FRED')
  6  and wait_class != 'Idle'
  7  /

EVENT                          TIME_WAITED/100
------------------------------ ---------------
enq: TM - contention                    351.05
SQL*Net message to client                    0


Thursday, June 08, 2017


I wondered what would happen if you tried to do a password expire on an externally identified user so I tried it out on an Oracle 11.2 database. As you might expect, it failed:
SQL> alter user system identified externally
  2  /
User altered.
SQL> alter user system password expire
  2  /
alter user system password expire
ERROR at line 1:
ORA-28010: cannot expire external or global accounts
I guess this is because if an externally identified user had to change his password, he would not be externally identified any longer. Also, a user cannot change itself to or from external identification unless it has the ALTER USER privilege.