SQL hanging on TEMP space usage Oracle Tips by Burleson Consulting

 

For more complete details on diagnosing a hung database, see the book "Advanced Oracle Utilities: The Definitive Reference".

In some cases you can see a "hang" situation when someone is modifying a file used for temp space.  IN these cases, the wait event for TEMP space will include:

 

SID 1467
EVENT enq: SS - contention
P1TEXT name|mode
P2TEXT tablespace #
P3TEXT dba
WAIT_CLASS# 0
WAIT_CLASS Other

 

Next, you can find the source of the hanging contention.  Here is a complete article on getting the values from p1, p2 and p3.

P1—The absolute file number for the data file involved in the wait.
 

P2—The block number within the data file referenced in P1 that is being waited upon.

P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:

select
   p1 "File #".
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'xxx';
 

 

This script was used to find the source of the TEMP usage, in this case, SS contention:

 

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';