Re: Use of TEMP tablespace other than sorting

Rick,

What do the following queries say:

select tablespace_name, contents, extent_management, initial_extent, allocation_type from dba_tablespaces where tablespace_name = '<yourtablespacenamehere>';

and

select name, value from v$parameter where name in ('sort_area_size', 'hash_area_size');

hash operations also use temp tablespace. However, I have seen this happen primarily in places where the so called temporary tablespace is defined with contents "permanent" i.e. not exactly a temporary tablespace. Happens when hash_area_size is much bigger than the uniform extent size of the temp tablespace.

In 9i however you cannot assign a user - a temporary tablespace which has its contents "permanent".

Anurag

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:bdgq5vgo1m50r16l2a40ilp7u49hokcbam_at_4ax.com...
> Using an SQL query with a join over six tables, the query breaks with
> the message "ORA-1652: unable to extend temp segment by 512
> in tablespace TEMP". This query does not do any sort operation, at
> least not explicitly.
>
> After having expanded TEMP four times consecutively up to 48 GB, I am
> still getting the same error. So I wonder what TEMP is used for beside
> sort operations. The tablespace is locally managed, uses uniform
> extents of 4 MB each. The sort area size is set to this size as well.
>
> Is there a way to monitor the use of space in TEMP by a particular
> session?
>
> Oracle 8.1.7 / Solaris 2.7
>
> Any hint would be appreciated.
>
> Rick Denoire
Received on Wed Feb 26 2003 - 21:24:46 CST