Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx, a.lia@xxxxxxxxxx
- Date: Mon, 8 Feb 2010 09:09:34 -0800 (PST)
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@xxxxxxxxxx> wrote:
From: Alessandro Lia <a.lia@xxxxxxxxxx>
Subject: ORA-01652: unable to extend temp segment by 128 in tablespace
TEMP...help
To: Oracle-L@xxxxxxxxxxxxx
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
Other related posts:
