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.
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.