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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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

Oracle Database Tips by Donald BurlesonDecember 16,  2015

Question:

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?

 

Answer:

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.