Hi

I wonder why i receive following error when i try to create index.

------

SVRMGR> create index yt_yritys_nimipuhelin on yritys (nimi,puhelin);
create index yt_yritys_nimipuhelin on yritys (nimi,puhelin)
                                      *
ORA-01652: unable to extend temp segment by 3596 in tablespace SYSTEM

--------

I check system tablespace and durring index creation it increases
one of the datafiles about 12 meg, then it prints that error message.
Datafile max size is 400 meg and it's used 300 meg, there is 100 meg
of free space!

jarno komulainen

You didn't indicate what OS the DB is running on but in general you
shouldn't have index and data tables in the system tablespace - create
separate tablespaces for data and indexes ; Also check to see if in
your operating system there is a limit on the number of extents that
can be allocated to a tablespace, regardless of max size of the
tablespace,

Hope it helps,
John Greco
Oracle DBA ( 7.3, Netware 4.11)

2. Dont'use SYSTEM-Ts for TEMP-Segments
   Use a large enough separate tablespace
   (and set it to TEMPORARY (avilable    since ORA7.3))

--
=====================================
Norbert Ferstl
DI Forsthuber GmbH
Kohlbauernstr. 17
A-2630 Ternitz, AUSTRIA
Tel: +43 2630 382500
Fax: +43 2630 3825014

=====================================

You can set the temporary tablespace for the user via the following method
in SQL*Plus or Server Manager.

alter user USERNAME temporary tablespace TEMP;

If you don't have a dedicated tablespace for temp segments, you should
create one.  If you're using 7.3 or greater, issue the following statement
after creation:

alter tablespace TEMP temporary;

Substitute the appropriate values as needed above.

Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Chris Hamilton - Oracle Database Admin.
AVANCO Intl. / US Army Corps of Engineers

http://www.serve.com/cowpb/chamilton.html

It may be not that simple. Oracle often produces MISLEADING messages.
For example, if I try to create HASH CLUSTER, parameters
HASHKEYS and SIZE effectively specify the space which will be allocated
to this cluster during creation. Let's say, database block
size is 2K, I specify SIZE 1700, then the number of allocated data
blocks will be equal to HASHKEYS. If INITIAL, NEXT, MAXEXTENTS
are too low, Oracle will not be able to allocate the required
space and it produces this completely misleading message
about temp segment