Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help
Date: Mon, 8 Feb 2010 09:09:34 -0800 (PST)
Message-ID: <655859.82440.qm_at_web32007.mail.mud.yahoo.com>
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.
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.
Alessandro
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 08 2010 - 11:09:34 CST