Need clarafication on temp tablespace

[a128]

According to the doc: "The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown."

So does that mean if there are more and more unique sorts coming in, the temporary tablespace will keep growing? Will the upcoming sorts reuse the space allocated for previous finished sorts? That is, once a sort finishes, is space allocated for it available to the future sorts?

Thanks,

[slimdave]

Originally posted by a128
Will the upcoming sorts reuse the space allocated for previous finished sorts? That is, once a sort finishes, is space allocated for it available to the future sorts?

Thanks,

Yes, and yes. you can monitor "real" usage with the v$sort_usage (i hthink) system view

[marist89]

The space is reused.

[samdba]

Hi,

If space is reused, why we get ORA-1652 error.

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

select sum(blocks*8192) from v$sort_usage is returning 2443182080 (2330 M).

At present, in OEM, it shows that out of 3000M, 2990 M is used and so we are getting ORA-1652 error showing that it is not able to extend it by another 10M. (1280*8192).

Little confuzed.

[TomazZ]

Look like temp tablespace is filled up.
3000M-2990M is probably less then 10M in your case.
2330M??? - would you select * from dba_segments where tablespace_name = 'TEMP'

[Dilippatel]

Try following

1. ALTER TABLESPACE tablespace_name COALESCE;

The above option may still fail if there is still not
enough contigious space.
2. Reevaluate the objects NEXT extent size and the percent increase
or add a datafile
or increase the size of same datafile.

[shashikumar]

1. Check out the columns tablespace_name and contents from dba_tablespaces view.
2. If the contents indicates that the tablespace is temporary then okay. Checkout the initial, next extents size and pctincrease parameters. Both should be equal.
3. Check out the sort_area_size parameter. use the formula
(multiples of sort_area_size + 1 oracle block size) as the size of the initial extent size.

check out

[Ramg]

and pctincrease should always should be 0

[slimdave]

Temp tablespaces are very simple

i) Make them locally managed
ii) Make them uniform extent size
iii) Make the extent size 1M
iv) Forget about them from then on.

That's all there is to it.

[marist89]

Originally posted by slimdave
Temp tablespaces are very simple

i) Make them locally managed
ii) Make them uniform extent size
iii) Make the extent size 1M
iv) Forget about them from then on.

That's all there is to it.

v) If you run out of TEMP space, that means you don't have enough TEMP to support your systems.