Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help
Date: Tue, 9 Feb 2010 09:41:49 -0800 (PST)
Message-ID: <604723.37472.qm_at_web32004.mail.mud.yahoo.com>
Yes, someone had emailed me to say I should put a disclaimer on my scripts for all the 10g databases I work in, (silly DBA girl and her ASH views! :() I hate not having anything lower to test out in, but ol' Jonathan has a view, (or query if you want to base off that, change the blocks to mb, etc...) that will work in earlier versions of Oracle since I'm deprived of anything but 10g and can't verify...:)
http://www.jlcomp.demon.co.uk/sort_usage.html
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 Tue, 2/9/10, Alessandro Lia <a.lia_at_tct-it.com> wrote:
From: Alessandro Lia <a.lia_at_tct-it.com> Subject: Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP...help To: "Kellyn Pedersen" <kjped1313_at_yahoo.com>, Oracle-L_at_FreeLists.org Date: Tuesday, February 9, 2010, 12:42 AM
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_at_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 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 Tue Feb 09 2010 - 11:41:49 CST