Doc ID 1905174.1 on My Oracle Support looks at how to resolve this wait event.
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 22.214.171.124 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
SQL> grant create session, create table to andrew
SQL> create user fred identified by bloggs
SQL> grant create session to fred
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
SQL> grant update on tab1 to fred
SQL> update tab1 set col1 = 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
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'
enq: TM - contention 351.05
SQL*Net message to client 0
Monday, August 14, 2017
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
SQL> alter user system password expire
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.