Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Unable to extend temp segment...
Jarno Komulainen <jkomula_at_server.seas.ucla.edu> wrote . . .
> 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