Clarity

Expand all | Collapse all

unable to extend temp segment by 128 in tablespace TEMP

  • 1.  unable to extend temp segment by 128 in tablespace TEMP

    Posted 09-04-2018 02:45 PM

    Jasper report has been scheduled, it says error "unable to extend temp segment by 128 in tablespace TEMP".

     

    Another exception says Error at SQL Error, If its error at SQL then why it shows "unable to extend temp segment by 128 in tablespace TEMP".

     

    Please advice.



  • 2.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Posted 09-04-2018 11:15 PM

    Badly performing SQL can use the TEMP space, which can then cause the error message you have seen.

     

    Please consider reviewing the SQL you are using in the report to determine if it needs to be tuned.



  • 3.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Broadcom Employee
    Posted 09-04-2018 11:18 PM

    Along with badly performing SQL you need to find out if your database memory allocation (SGA/PGA) is correct



  • 4.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Broadcom Employee
    Posted 09-05-2018 12:58 AM

    Hi 

     

    I think when Oracle does not have enough space to store intermediate data when executing operations like joining tables, etc....,  this error message will be produced.

     

    Please check SQL which caused this error and you may find badly join statement.

    or contact your Database administrator and consider about expansion of TEMP tablespace.

     

    "ALTER TABLESPACE TEMP ADD DATAFILE" statement to add one or more files to the TEMP tablespace. 

     

    Regards,

    Shoichi



  • 5.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Posted 09-05-2018 10:51 AM

    So what does the number 128 indicates ?



  • 6.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Posted 09-05-2018 10:53 AM

    Suppose if a report schedule has started , in the Background the Query run and it may have 1000 lines or more, so will this Temp table holds the data ? and after the Query runs successfully will the Temp table clears its data ?



  • 7.  Re: unable to extend temp segment by 128 in tablespace TEMP

    Broadcom Employee
    Posted 09-05-2018 11:46 AM

    Oracle manages the temp pretty efficiently and if you are continuously running this error it could be either the TEMP size is set too small or your oracle memory sizing is not adequate or the query is bad which is keeping too much data in temp and it can happen due to all the above reasons.