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