Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
“enq: SS – contention” wait event may occur in problems with temp tablespace usage in Oracle RAC databases. At the same time, SMON may be preventing the use of temp space by other processes when cleaning large processes.
With the following query, you can detect the operations that have a “enq: SS – contention” wait event.
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT DISTINCT u.username, u.osuser, w.event, w.p2text AS reason, ts.name AS tablespace, NVL (ddf.file_name, dtf.file_name) FROM v$session_wait w, v$session u, v$tablespace ts LEFT OUTER JOIN dba_data_files ddf ON ddf.tablespace_name = ts.name LEFT OUTER JOIN DBA_TEMP_FILES dtf ON dtf.tablespace_name = ts.name WHERE u.sid = w.sid AND w.p2 = ts.TS# AND w.event = 'enq: SS - contention' |
Solution
Re-create the existing TEMP table space to resolve the problem. You may want to read the article named “How To Recreate Temp Tablespace”
