Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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

Oracle Database Tips by Donald BurlesonDecember 16,  2015


I'm receiving the following error messages when trying to insert records (error #1) and update records (error #2).

 #1. ORA-01654: unable to extend index SYSTEM.TDW_CHARGES_IDX04 by 112085 in tablespace TDWTS_INDEX

 #2. ORA-01654: unable to extend index SYSTEM.TDW_DAILY_FEED_IDX04 by 39488 in tablespace TDWTS_INDEX

All of the indexes are stored in a single physical file named: TDW_INDX.ORA which is 8.3gig in size. The application is running on a Windows 2000 server, using NTFS file system and Oracle Server version 7.3. All of my indexes and tablespaces are created with unlimited extents.

I can drop and rebuild the indexes successfully, but after letting the application run a while, I begin getting the error messages again.  Oracle documentation says to add another datafile for the tablespace, but I'd like to avoid doing this. This application is running at several sites and I would like to keep each site consistent as far as files, etc.

I know that Oracle has a bug with version 7 files keeping the unlimited feature set, but I've checked and this doesn't seem to the problem.

Any ideas?



The Oracle documentation notes the following aobut ORA-01654:

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.


Let's start with a few questions:

A. Do you have diskspace available?                                                                      

B. Is the tablespace TDWTS_INDEX set for autoextending, if so will the current size + extend size > available diskspace?

C. How many extents are the current indexes?


I would be leery of a file > 4gig on Windows. I have seen the file actually truncate back to a 4gig limit (which WILL hose the database).  I've had problems with files larger than 4GB (and even if you dont now you might when you migrate to 8i/9i/10g).  

I would suggest the best thing to do is:

 1) create a new tablespace with 2 (or more) datafiles of 3.9GB each (or with autoextend on maxsize 3900MB) .

2) Rebuild your indexes into the new tablespace.

3) Drop your old tablespace.

AND repeat this at all your sites so they are consistent.


In the past, I haven't allowed any of my customers to have datafiles larger than 3995m.

Tablespace extensions are expensive (disk hits). You can try to size the tablespace so that there are minimal extension, but it will happen.  If it needs to extend, it will.  When it does, Oracle will have to "touch" each data block of the new extent, which is costly.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.