Re: Determining TEMP tablespace size

In article <3rqhu4$9d0_at_blues.axionet.com> syam_at_axionet.com (Steve Yam) writes:
>From: syam_at_axionet.com (Steve Yam)
>Subject: Re: Determining TEMP tablespace size
>Date: Thu, 15 Jun 95 17:05:27 PST
 

>In article <3rnqv4$nc3_at_netaxs.com>, tyson_at_netaxs.com says...
>>
>>An Oracle Forms query died with the following error:
>>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
>>
>>Our DBA wants to know how big to make the TEMP tablespace. How can we
 determine what size
>>to make the TEMP tablespace, not only to correct the current problem,
 

>There is a view belonging to SYS, dba_free_space which keeps all free
>space of each tablespace in segment level. You can check this view by DBA
>privilleges account. It may be the problem of fragmentation instead of
>out of space.
 

>Besides, assigning TEMP is the role of DBA. BUT using TEMP is the role of
>application development. TEMP should only be used for sorting, grouping
>transactions. TEMP usage also related to the SORTING AREA in SGA. Tuning
>SORTING AREA may have better effect instead. Anyway, it is hard to say
>who should take the responsibility. Why responsibility is that important?
>It is full of fun in DB tuning.
 

>Steve Yam.

You could also try exmaining the query that is causing the error. Oracle sometimes writes temporary segments to hold intermitant query results while joining across tables. If the query is inefficient it may be generating large quantities of intermediate results, thus filling up the temporary segment.

The gut reaction to this problem is to add extra files to the TEMP tablespace, but it may turn out to be as simple as adding another index or re-coding the query. Try running EXPLAIN PLAN on the query to see how efficient it is.

Hope this helps,

Martin.

The opinions expressed above are mine, and not that of my employer. Received on Fri Jun 23 1995 - 00:00:00 CEST