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@xxxxxxxxxxxxx ; a.lia@xxxxxxxxxx 
  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@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: