RE: unable to extend temp

From: Mark W. Farnham <>
Date: Wed, 31 Jul 2019 18:35:46 -0400
Message-ID: <034a01d547f0$4c046fe0$e40d4fa0$>

Not exactly aware of what causes it (and a client has an active SR open which became low priority because, drumroll):  

IF it is the same problem you can add the amount of temp you need as a second temp in the group and then drop the first one.  

My working theory is that there is a disconnect between releasing temp objects in the database and freeing them in the temp area. So from the database viewpoint and actual temp being actively used you have plenty, but there is nothing to allocate left. Lack of access to the host in a DBaaS environment prevented me from verifying my working theory.  

Let us know how you make out, especially if my suggested workaround is effective. If you have access to the host disks, take a look at the place temp lives from the OS, too.  

The problem has resurrected itself twice, both times preceded by temp operations becoming a factor of 100x slower than usual (which I *suspect* is hunting for places to squeeze out another extent.)

Since there is so little operational overhead to add temp/drop old temp, that is now a rule out exercise when unable to extend is seen. Same environment.  

Good luck,  


From: [] On Behalf Of Jack van Zanen Sent: Tuesday, July 30, 2019 6:57 PM
Subject: unable to extend temp  

Hi All    

Oracle RAC exadata OEL  

In our test PDB we had an issue yesterday where we were getting s lot of unable to extent in tablespace temp messages.  

Fair enough so I checked the sort_usage but this came back with hardly any temp being used at the time.

I have searched metalink and found a bug that was fixed in in version 11 or so..

Anyone aware of anything that could cause this  


, b.segfile#
, b.segblk#
, ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
, a.inst_ID
, a.SID
, a.serial#
, a.username
, a.osuser
, a.program
, a.status
FROM gv$session a
, gv$sort_usage b
, gv$process c
, gv$parameter p
WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr -- AND b.TABLESPACE='TEMP2' ORDER BY a.inst_ID , b.TABLESPACE
, b.segfile#
, b.segblk#
, b.blocks;

at the time we had parameter temp_undo_enabled set to true (testing this across all our test environments)  

so I also checked  

select * from gv$tempundostat  

which also did not return a usage consistent with running out of temp.    

The bug I found led me to a couple of sql statements which do not mean a whole lot to me  

select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)"  from gv$sort_segment
 where tablespace_name='TEMP'
 order by 1;  

 select sum(bytes)/1024/1024/1024, owner from gv$temp_extent_map group by owner;  

  select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;  

what are they supposed to represent as the only information the document provided was that it could assist with troubleshooting      

Jack van Zanen  


This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation
Received on Thu Aug 01 2019 - 00:35:46 CEST

Original text of this message