Ora-1654 unable to extend index by 12800 in tablespace

[mrn]

Let me start by saying I'm not an Oracle Bod

I'm getting the following error

Ora-1654: unable to extend index ***.PK_*** by 12800 in tablespace ****

What do I need to do to correct this error?

Regards
--
| Mike Nixon
| Unix Admin
|

http://www.instantflashshop.com

----------------------------

 

[stefanhei]

Hi.
The message tells you, that you've run out of space in the given tablespace.
Solution is making more space available for the index you'd like to create. This could be done by
1) enlarging the tablespace by resizing datafiles:
Look for ALTER DATABASE in your doku. You'll need to know the file to resize: use SELECT file_name from dba_data_file where tablespace_name = 'TS_Name_Given_In_Error'
2) deleting objects which are no longer needed or moving objects to another tablespace (ALTER TABLE, ALTER INDEX)

Stefan

 

[ThomVF]

From ORACLE docs:

ORA-01654 unable to extend index string.string by string in tablespace string

Cause: Failed to allocate an extent for index segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


The tablespace where this index resides is full or cannot allocate the next extent per the index configuration.

You could do one of the following:
1) Add a datafile to the tablespace or enlarge the tablespace file. Easiest thing to do. Most likely Requires DBA support.

Sample add:
ALTER TABLESPACE <tablespace>
ADD DATAFILE '<data file spec>' SIZE <size>M|K;

Sample extend (if you cannot add another file):
ALTER DATABASE DATAFILE '<filename>' RESIZE <size>M|K;


2) Rebuild the index using different storage parameters - for example, check PCTINCREASE or NEXT extent size. Might need to check with the DBA if you plan to go this way.
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

If I remember correctly, the additional space of 12800 is in units of blocks, not bytes. If you have a 2k block size that means Oracle is trying to increase the index size by about 25 Mb. It's even more if you are using a larger block size.

You should check and see if this is a reasonable extent size for your index. It's very likely you don't need that large of an extent. Perhaps you have pctincrease set at 50 and are trying to allocate much more space than you really need.

 

[Guest_imported]

Just a note,

From what I can tell, this is a primary key on a very large table that you are trying to re-enable, or your tablespace is very small. Make sure you are sending it to the correct tablespace as the index will build in the default tablespace if you do not specify an alternate tablespace (quotas must be set for other tbspc).

If you want to rebuild, you can issue;

alter index your_index rebuild tablespace other_tbspc;