Unable to extend temp segment... 
Author Message
 Unable to extend temp segment...

                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



Mon, 03 Apr 2000 03:00:00 GMT
 Unable to extend temp segment...

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)

Quote:

>            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




Mon, 03 Apr 2000 03:00:00 GMT
 Unable to extend temp segment...

Quote:

>                 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


1. Create large indexes neet large temp-segments

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

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



Mon, 03 Apr 2000 03:00:00 GMT
 Unable to extend temp segment...


Quote:
> 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

The problem is that your TEMPORARY tablespace setting for the user is set
to SYSTEM.  This causes any temporary segments that are created as a result
of the user's SQL to be created in the SYSTEM tablespace (not a good idea
for performance and storage reasons).  The segments are created (and
subsequently dropped automatically) when creating an index, running a query
with an ORDER BY or GROUP BY statement, and other operations.

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



Mon, 03 Apr 2000 03:00:00 GMT
 Unable to extend temp segment...

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

Quote:

>                 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




Fri, 07 Apr 2000 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

2. ORA-01652: unable to extend temp segment by 256 in tablespace TEMP

3. ORA-01652: unable to extend temp segment by 512 in tablespace TEMP

4. 01652: unable to extend temp segment

5. ORA-1652: unable to extend temp segment by 7680 in tablespace

6. ORA-01652 unable to extend temp segment by num in tablespace name

7. ORA-1652: unable to extend temp segment by 8192 in tablespace

8. ORA-1652: unable to extend temp segment

9. ORA-01652: unable to extend temp segment by 1598 in tablespace

10. ORA-01652: unable to extend temp segment by 1598 in tablespace

11. extend temp segment - ora 01652

12. Unable to extend rollback segment problem


 
Powered by phpBB® Forum Software