Home » SQL & PL/SQL » SQL & PL/SQL » Unable to extend in TEMP tablespace because of GROUP BY clause
Unable to extend in TEMP tablespace because of GROUP BY clause [message #224997] Fri, 16 March 2007 08:22 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member

Hi,

We are experiencing "unable to extend segment error in TEMP tablespace" for a query. The query selects nine columns from a join of seven tables and has a group by and order by clause. The select clause and the Group by clause are exactly the same. And without the group by clause, the query executes successfully whereas when we include the group by clause, it fails with the TEMP tablespace error. Could anyone please help us with your inputs?

Thanks in advance,
Ganesh
Re: Unable to extend in TEMP tablespace because of GROUP BY clause [message #225003 is a reply to message #224997] Fri, 16 March 2007 08:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your TEMP tablespace is too small for the SORT operation that is required for the GROUP BY.

Increase your TEMP tablespace size.
Re: Unable to extend in TEMP tablespace because of GROUP BY clause [message #225584 is a reply to message #225003] Tue, 20 March 2007 07:30 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
Total records that have to be sorted is just 892 and the temp space available is 30 GB. As mentioned previosly, we got system exception "unable to extend temp space".

We added ROWNUM condition in our query and this time query executed properly. Can you please let us know whats the impact of this ROWNUM in query.

Plan says "RECURSIVE EXECUTION OF 'SYS_LE_2_0'" without ROWNUM

Thanks in Advance.
Re: Unable to extend in TEMP tablespace because of GROUP BY clause [message #225589 is a reply to message #225584] Tue, 20 March 2007 07:46 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Was it "unable to extend temp segment in tablespace <X>" or "unable to extend TEMP tablespace?" Big difference.
Re: Unable to extend in TEMP tablespace because of GROUP BY clause [message #225605 is a reply to message #225589] Tue, 20 March 2007 08:18 Go to previous message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
It is "unable to extend segment error in TEMP tablespace".

query was select col1, col2 from ... group by col1, col2.

It made difference when changed to

select col1, col2, count(1) from ... group by col1, col2.

Thanks in Advance.
Previous Topic: procedure / package
Next Topic: Sending E mail to a Distribution List using UTL.SMTP
Goto Forum:
  


Current Time: Sun Dec 04 00:43:43 CST 2016

Total time taken to generate the page: 0.08776 seconds