Home » SQL & PL/SQL » SQL & PL/SQL » Error ORA-01652 while running select query (Oracle 10g)
Error ORA-01652 while running select query [message #310723] Wed, 02 April 2008 03:55 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
A select query returning millions of records is returning the following error.

SQL Error [
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

We requested DBA to increased the TEMP tablespace. He increased it to 20GB and also, changed to Autoextent.

When we executed the query again, we got the same error ?

What could be the reason of this issue and how it can be resolved ?
Re: Error ORA-01652 while running select query [message #310732 is a reply to message #310723] Wed, 02 April 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What could be the reason of this issue

You need more temp space.

Quote:
how it can be resolved ?

Either increase temp space or modify the query to make it use less temp space.

Regards
Michel
Re: Error ORA-01652 while running select query [message #310739 is a reply to message #310732] Wed, 02 April 2008 04:33 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
please Post your query with

1) Table structure
2) explain plan
Re: Error ORA-01652 while running select query [message #310743 is a reply to message #310739] Wed, 02 April 2008 04:42 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
There's no ORDER BY in the query.
Can you pleae tell me apart from sorting what other processes happen in TEMP tablespace ?
I need to get query and explain plan from the DBA, I'll try and then post them shortly.
Re: Error ORA-01652 while running select query [message #310798 is a reply to message #310723] Wed, 02 April 2008 08:00 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You often need temp space with sorting, but note that an order by clause is not the only reason that you would sort. Don't forget other options, such as distinct, group by, union, etc that may also require a sort. Sorts also might be used in certain join operations, such as a sort merge join.
Re: Error ORA-01652 while running select query [message #310804 is a reply to message #310798] Wed, 02 April 2008 08:11 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And some function like analytic ones and other internal stuff if Oracle estimates it can save time.

Regards
Michel
Previous Topic: remove comments from all_source output
Next Topic: outer join
Goto Forum:
  


Current Time: Fri Dec 02 22:46:37 CST 2016

Total time taken to generate the page: 0.25086 seconds