Re: Temp Tablespace

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/06/06
Message-ID: <960320220.10430.1.nnrp-06.9e984b29@news.demon.co.uk>#1/1

This is expected behaviour for temporary tablespaces (i.e. contents = TEMPORARY, or create temporary tablespace).

Any space required for sorting is allocated to a single segment managed by the instance. This space is rarely released by the instance. (Although in 8.1 instantiation of global temporary tables may result in sort space being release).

If you are regularly getting 'unable to allocate' then it is likely that you need a larger temporary. have a look at V$SORT_SEGMENT for columns like:

max_allocated
max_sort_size

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

jackwithman_at_my-deja.com wrote in message <8h8m2a$rnp$1_at_nnrp1.deja.com>...


>Hello Everyone,

>

>I am very curious of something.

>I'm running under oracle 8.1.5 and my temp tablespace (200 M) is always

>full (99%). As soon as I shut down my database, the temp tablespace is

>purged but when I bring the database back up it starts growing and in

>half a day, temp is choking and I have sometimes failures to extend.

>

>Looks like a lot of space is lost.

>Is there any way to 'shrink' it, other than bringing the tablespace

>offline and online right after?

>

>ThanXs for your help.

>

>                                        Jack

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.

Received on Tue Jun 06 2000 - 00:00:00 CDT