Home » RDBMS Server » Performance Tuning » INDEX CREATION (oracle 9.2x , UNIX)
INDEX CREATION [message #278990] Tue, 06 November 2007 09:22 Go to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi,


Iam trying to create an index to a table called product_account.Data are already poulated in to it. Iam creating index by using three columns of that table.When i execute build using the index which i created it shows me an error message

Error Message - unable to extend temp segment by 8 in tablespace

Iam using SQL developer to create and build on the index.

What could be the possible solution that i can undertake?

Thanks and Regards,
Balaji
Re: INDEX CREATION [message #278994 is a reply to message #278990] Tue, 06 November 2007 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
increase the size of the tablespace
Re: INDEX CREATION [message #279129 is a reply to message #278994] Wed, 07 November 2007 01:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle has to sort the contents of the indexed columes in order to create the index. Sorts take up TEMP space - lots of it for big data volumes.

You can temporarily increase TEMP for the index rebuild and then deallocate it later.

Alternatively you should look into partitioning the index. Partitioning will reduce the space required to sort the data initially, and will permit you to rebuild it one partition at a time (should you ever need to) in the future.

Ross Leishman
Re: INDEX CREATION [message #279166 is a reply to message #279129] Wed, 07 November 2007 03:29 Go to previous message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi

Thank you so much. When i created an index through SQL Develeoper it was using DATA_S as the tablespace.I tried using the query below

CREATE INDEX XYZ.PAC_INDX1_TEST2 ON XYZ.PRODUCT_ACCOUNT (COLUMN NAMES) TABLESPACE INDEX_M;

its working fine now.

Regards,
Balaji
Previous Topic: Slow performance
Next Topic: How to improve a group by sql stmt (merged)
Goto Forum:
  


Current Time: Thu Dec 08 00:40:56 CST 2016

Total time taken to generate the page: 0.10215 seconds