Q
Problem solve Get help with specific problems with your technologies, process and projects.

Solving ORA-1652 error

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

Some data regarding this:

TABLSPACE INIT_EXT NEXT_EXT MAX_EXT PCT
------------------------- --------------
SYSTEM 16384 16384 505 50
TEMP 10485760 10485760 0
RAMS_DATA 516096 516096 500 0
RAMS_INDEX516096 516096 500 0
RBS 516096 516096 500 0

Some Oracle parameters:
sort_area_size :10 MB
db_block_size :8192

We have a large amount of data on which we run a complex query, containing groupby clause to get some reports. We have also tried to increase the temporary tablespace from 1 GB to 2 GB but the problem persists. This problem is quite critical for our application.

The ORA-1652 error simply states that your tablespace does not have enough free space to satisfy a request for a new extent. So you'll have to add more space or figure out why you don't have enough free space ther already. You didn't give me much more to go on to help you so I'll just add some more information. Hopefully, one of these pieces will assist you. First, a 1 or 2GB TEMP tablespace is not that particularly large in today's environments. I've seen TEMP tablespaces of 10GB or more. So it may be that you simply don't have a large enough TEMP tablespace to support all of your sorting needs. Second, there is a bug in Oracle 8i and 9i when using Locally Managed Tablespaces (LMTs) for TEMP tablespaces when the applications maintain persistent connections, that the instance does not release unused temporary segments for future use by another process. The way to correct this problem is to revert back to Dictionary Managed Tablespaces for TEMP.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close