Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help

V$SORT_USAGE reports extents currently in use and when those are no longer needed by one session they are freed so other sessions can use them.  It's entirely possible that the transaction consuming TEMP space needs more than 512 MB, causing the error you're seeing.  You need to monitor V$SORT_USAGE while the transaction is running to see if you are truly running out of space (and I expect that you are).  Sort activity is not the only operation that can consume TEMP space which is why 10g and later releases have modified that view to become V$TEMPSEG_USAGE, which reports sort and hash activity in the defined temporary tablespace(s).  That being said the query you've posted should provide enough information to you if you run it at regular intervals while the suspect query/DML is being executed.
 
David Fitzjarrell





________________________________
From: Alessandro Lia <a.lia_at_tct-it.com>
To: Kellyn Pedersen <kjped1313_at_yahoo.com>; Oracle-L_at_FreeLists.org
Sent: Tue, February 9, 2010 2:42:22 AM
Subject: Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help


thank you for your reply.
 
your script return some error (my release is 9.2.0.8.0, some fields not exist in those tables), anyway I ran the following:
 
 
 SELECT   A.tablespace_name tablespace, D.mb_total,
          SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
          D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM     v$sort_segment A,
          (
          SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
          FROM     v$tablespace B, v$tempfile C
          WHERE    B.ts#= C.ts#
          GROUP BY B.name, C.block_size
          ) D
 WHERE    A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;
 
 
and returns:
TABLESPACE     MB_TOTAL    MB_USED    MB_FREE
------------------------------- ----------    ----------            ----------
TEMP                         512           0                 512
 
 
It is very strange it looks like TEMP full by OEM.

----- Original Message ----- 
>From: Kellyn Pedersen 
>To: Oracle-L_at_FreeLists.org ; a.lia@tct-it.com 
>Sent: Monday, February 08, 2010 6:09 PM
>Subject: Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help
>
>
>Are you sure this is what used up the temp space or if this was just the victim of no temp space left? (I sure feel like I'm asking this a lot lately... :))
>
>Run it again and look at your actual temp space usage at the database level, not just your explain plan...
>select
>, swa.operation_type
>, trunc(swa.work_area_size/
>, trunc(swa.max_mem_used/
>, trunc(swa.tempseg_size/vst.sql_text, swa.sql_id, swa.sid1024/1024) "PGA MB"1024/1024)"Mem MB"1024/1024)"Temp MB"fromv$sql_workarea_active swa, v$session vs, v$sqltext vstwhereswa.sid=vs.sidandswa.sql_id=vs.sql_idandvs.sql_id=vst.sql_idandvst.piece=0order
>Kellyn Pedersen
>Multi-Platform DBA
>I-Behavior Inc.
>http://www.linkedin.com/in/kellynpedersen
>www.dbakevlar.blogspot.com
>
>"Go away before I replace you with a very small and efficient shell script..."
>
>--- On Mon, 2/8/10, Alessandro Lia <a.lia_at_tct-it.com> wrote:
>
>
>>From: Alessandro Lia <a.lia_at_tct-it.com>
>>Subject: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help
>>To: Oracle-L_at_FreeLists.org
>>Date: Monday, February 8, 2010, 4:18 AM
>>
>>
>>I create one view (test env 9.2.0.8.0) and I ran a simply query (select * from view_name) without any problem.
>>Then I cut 2 fields from the select that generate the view (the where clause still remain the same) and when I ran the same query (select * from view_name) it returns: "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP".
>>The execution plan shows :
>> 
>>| Id   | Operation                                       |  Name                 | Rows   | Bytes |TempSpc| Cost  |
>>----------------------------------------------------------------------------------------------------------------------------------------------
>>|  11 |       HASH JOIN                              |                            |         1 |    222 |        57M|  4178 |
>>|  12 |        MERGE JOIN CARTESIAN       |                            |  1364K|    41M|               |  1962 |
>> 
>> 
>>These steps there were not with the two fields I cut before. Anyone have any idea why it can happen?
>>I just cut 2 fields not concerning any join in "where" clause and it appears TempSpc 57M never show before.
>> 
>>ps.
>>before I found the TEMP tbs with no file attached. After I added 2 tempfile 256M each.
>> 
>> 
>>Thanks in advance.
>> 
>>Alessandrobyswa.sql_id; 
>
>  
>


      

--
http://www.freelists.org/webpage/oracle-l