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
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