Re: Temporary Tablespace

Karl,

You can put a temporary segment in any tablespace. The only reason to set a tablespace temporary is it will be skipped by the logwriter. In an ideal situation every temporary segment should be allocated in a temporary tablespacce.
Where your temporary segments are allocated is determined on user level. You should make sure every user has his temporary tablespace pointing to the correct tablespace by issuing
alter user <username> temporary tablespace <tablespacename>

Oracle starts creating temporary segments as soon as the size of a sort (or create index, or group by) exceeds whatever your sort_area_size is. The size of the extents is determined solely by the default storage clause of the tablespace.
You could easily see what is going on by issuing a select like this select tablespace_name, segment_name, bytes from dba_segments
where segment_type = 'TEMPORARY'
and/or run $oracle_home/rdbms73/admin/utlbstat (start sampling statistics)
and /utlestat in the same dir a few hours later.

Each sort job does allocate one temporary segment.

Hope this helps,

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe
sybrand.bakker_at_bentley.nl

Karl Baier wrote:

> Hi,
>
> We are running 7.3.3 on Sun E4000 and we have got a TEMP tablespace
> of about 1 GB in size, defined as temporary. Short after startup,
> the only segment in the TS grows until there is no more space. I see
> sort jobs, but no sorts on disk (we have 8 MB sort_area_size).
>
> Does anyone have an idea, how/why this space is allocated? May be
> each sort job allocates one extent? But when the TS was full, we
> only had 25 sessions and the temp segment had 54 extents, 16 MB each.
>
> Regards
> Karl
> --
> ----------------------------------------------------------------
> Karl Baier Siemens Business Services - SBS IP 36 M
>
> Phone: +49 89 636 48096
> Email: mailto://Karl.Baier@mchp.siemens.de
> Nerv: mailto://baier.muc@sni.de
> ----------------------------------------------------------------
Received on Thu Nov 13 1997 - 00:00:00 CST