Re: help: Oracle space problem. Error code ora-01652

Hi Mary. I bet you get lots of answers to this. There are a couple of things you need to check. Firstly, is there enough space in the tablespace where you are creating temp segments? Note that you can alter the temporary tablespace with "ALTER USER TEMPORARY TABLESPACE <foo>" if there is another tablespace where it would be more appropriate. Secondly, you need to check the default STORAGE parameters for the tablespace you select for temporary segments to see what size they are created and extended with, and whether the extension size is increased dynamically (PCTINCREASE). Your DBA should be able to make sense of this. Also, it may be possible to get misleading messages so check all the same things for the rollback segment tablespace. Finally, there are some init.ora parameters which may be relevant - look at the meaning of sort_area_size and sort_area_incr (this last is actually more relevant to building indexes, I can't remember whether it applies to sorts while querying) and there may be a sort_to_disk or similar parameter?

In article <1997Dec30.191211_at_latte.memphis.edu>, hchen_at_cc.memphis.edu writes
>I was running a big query again Oracle 7.3.3 at NT server. I got error message
>says:
>
>"ORA-01652: unable to extend temp segment by 315 in tablespace SELMC"
>*SELMC is the name of tablespace.
>
>I told the DBA and he increased the segment space for me. I run the same query
>again, I got the same error, but it said unable to extend temp segment by 417.
>Then the DBA drop the old segment and created new temp segment with bigger
>size, but when I run the query again, I still got the same error code and now
>it said unable to extend temp segment by 1595. Then I tried disconnect to the
>database and then run the same query, unfortunately, I still got the same error
>code and now it is said unable to extend temp segment by 5120!
>
>Our tablespace is 175 meg, and used up 82 meg. The roll-up segment is 30 meg.
>There are about 90,000 records in the fact table.
>
>My questions are:
>1. 1.How to deal with this error code?
>2. 2.Why I still get the same error after the DBA increased the space?
>3. 3.Why the unable to extend temp space become bigger and bigger after each
>query?
>
>I am new to Oracle, Please help. Any suggestion are welcome and appreciated!
>You can either reply this mail or send reply to mchen_at_netbasecomp.com.
>Thank you!
>
>Mary Chen.
>
>
>
>
 

-- 
Tom Cooke