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

From: Kellyn Pedersen <>
Date: Mon, 8 Feb 2010 09:09:34 -0800 (PST)
Message-ID: <>

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 vst.sql_text, swa.sql_id, swa.sid
, swa.operation_type
, trunc(swa.work_area_size/1024/1024) "PGA MB"
, trunc(swa.max_mem_used/1024/1024)"Mem MB"
, trunc(swa.tempseg_size/1024/1024)"Temp MB"
from v$sql_workarea_active swa, v$session vs, v$sqltext vst where swa.sid=vs.sid
and swa.sql_id=vs.sql_id
and vs.sql_id=vst.sql_id
and vst.piece=0
order by swa.sql_id;


Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.

"Go away before I replace you with a very small and efficient shell script..."

  • On Mon, 2/8/10, Alessandro Lia <> wrote:

From: Alessandro Lia <> Subject: ORA-01652: unable to extend temp segment by 128 in tablespace To:
Date: Monday, February 8, 2010, 4:18 AM

I create one view (test env 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.

before I found the TEMP tbs with no file attached. After I added 2 tempfile 256M each.

Thanks in advance.

Received on Mon Feb 08 2010 - 11:09:34 CST

Original text of this message