Re: TEMPORARY SEGMENTS

When you do operations that require sorting to be done, Oracle first tries to use memory area defined by SORT_AREA_SIZE, but if your sort is bigger than this size then the temporary segments on the disk are used to complete the sorting.

Sorting is done internally by Oracle when you create the INDEXES, and the tablespace defined as TEMPORARY TABLESPACE for that user is used. It seems for your user which is creating index has an assignment to INDEX tablespace as temporary tablespace.

To check the temporary tablespace for this user you can issue following query while logged in as that user:-

select temporary_tablespace from user_users;

You will see that temporary tablespace for this user is assigned as INDEX tablespace.

This is normally not the case.

To correct this assign proper temporary tablespace for this user.

Your DBA can alter the teporary tablespace to a new tablespace which is created for temporary segments purposes.

The command to change the TEMPORARY TABLESPACE IS:

ALTER USER <USER_NAME> TEMPORARY TABLESPACE <TEMP_TABLESPACE_NAME>;

> max # extents reached in temp segment in tablespace INDEX
> ^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^

The error message you have mentioned indicates that MAX NUMBER OF EXTENTS has reached, this might have happened when INDEX tablespace was being used, the sort process was allocating new segments in that tablespace which reached a maximum allowed for that tablesapce. Reason of this could be the extaent sizes of the tablespace INDEX are small.

For this the tablespace should have bigger extent sizes.

Hope this helps,

Manoj Jain
OCP/Chauncey Certified Oracle DBA

In article <35C8A669.41C67EA6_at_ornl.gov>,   D S Tharpe <i5t_at_ornl.gov> wrote:
> Do all tablespaces create their own temporary segments when
> needed? Does anyone have any insight on this? I thought temporary
> segments were only created in the TEMP tablespace. My temporary
> tablespace is set to the TEMP tablespace, but it was not until today
> when I was creating indexes in my INDEX tablespace that I encountered
> the error which caused me to question temp segments. I increased the
> storage parameters on the INDEX tablespace and the index created fine
> then, but I am very confused by temp segments in a NON-TEMP tablespace.
>
> max # extents reached in temp segment in tablespace INDEX
> ^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^
>
> Thanks!
>
> D. Tharpe (i5t_at_ornl.gov)
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 05 1998 - 17:27:11 CDT