Re: Index and order by on Oracle 7.1

WANG SHIANG HUEY wrote:
>
> Hi, Oracle guru:
> I have created an index based on certain key in a table, and
> tried to run query sort by the field I defined in index,
> I got message ORA-001652 unable to extend temp segment by ###
> in tablespace SYSTEM
> after browsing documentatin, I increase sort_area_size to 32000 (16382
> by default), it still didn't solve the problem. Any body know
> what that message is all about? SYSTEM tablespace still has more 50%
> free. Thanks in advance.
>
> --
> Shiang-Huey Wang
> Leather Center, Inc.
> sherry_at_leathercenter.com
> wshiang_at_a.cs.okstate.edu

It simply means that there is insufficient space in the tablespace to do the sort operation. The only way to overcome this is to increase the size of the tablespace by adding additional data files to it.

I would suggest that you create an extra tablespace (TEMP) for the purpose of peforming sorts etc. It is usually better to reserve the system tablespace for the storage of Oracle's data dictionary, and create additional tablespaces for the storage of user objects, indexes, rollback segments and temporary use. This way you will have greater control over the location, size etc. of your database.

-- 
Mike Biggin	<mbiggin_at_denr.sa.gov.au>
Department of Environment and Natural Resources, South Australia
Telephone:	Oz: (08) 8204 9217; International: (618) 8204 9217
Mail:		GPO Box 1047, Adelaide, South Australia 5001