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

hchen_at_cc.memphis.edu wrote:
>
> 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.

Hi Mary,

lot's of questions in th early morning.

Q2: the space increased still doesn't fit. Extimate the number of records your query will return, multiply this number by the average size of the records returned, then you'll get an estimation of the temp space your query will need.

So, one reason that temp space isn't sufficient is often that you missed to encode a join you intended to be there so that your query returns the cartesioan product of one or more table which leads to an enorous number of records returned. Thus eating up all temp space. Check this first. (May be you you add something like 'WHERE <primary key> <= 4' so you can see wether you get duplicated records.)

If this is all checked and your query will need as much temp space beg your DBA to create a privat temp space for you wich can hold your estimated number of records.

Q3: I think itsn't important that failed allocated space increases. It's just the amount of space your query want's to allocate from temp space and can't alocate anymore.

-- 
Regards

Matthias Gresz    :-)