13 Replies Latest reply: Dec 29, 2013 11:35 PM by Sitanshu. RSS

ORA-1652: unable to extend temp segment by 128 in tablespace  TEMP

Sitanshu. Newbie
Currently Being Moderated

Hi all,

we are getting ora 1652 error since past few days i have increased the temp table space to almost 20gb but still it keeps throwing the error

 

Following errors written to the Alert log file. Please verify

**********************************************************************

Date        : 12/26/13 22:44:01 Thursday

**********************************************************************

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP

 

after some research  i found that below query is causing huge sort  operation & flooding the temp space

 

select distinct s.serialnr, s.shipdate, s.cpc, s.country, s.description, s.soldt

oparty, s.shiptoparty, s.ean_code, s.shipserialnr from registration_crm_serial_n

umber s left join registration_crm_serial_number s2 on s.serialnr = s2.shipseria

lnr where (s2.serialnr = :1 or s.serialnr = :2) and s.shipserialnr is null

 

i have only 10gb space remaining on my mount point & cannot increase any more space for temp table space.

 

database version -10g

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points