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 consistant 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 the problem.
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 ?
D. I would be leary of a file > 4gig on Windows. I have seen the file
actually trucate back to a 4gig limit (which WILL hose the database).
B. datafile is set for autoextend (below is create statement)
create tablespace TDWTS_INDEX datafile '&2\tdw_indx.ora'
size 400M autoextend on next 200M maxsize UNLIMITED
default storage(initial 050K next 050K pctincrease 50 maxextents UNLIMITED);
C. below is extent info (is this what you're asking?)
Thanks for the info. I was under the impression that NTFS allowed
files to grow to the device's capacity. Is this not right? I've been
running several sites with 8G database files for a couple of years, but
if I'm about to have a trainwreck, I'd like to try to head it off.
I've seen it happen 1 time... I haven't allowed any of my customers
to have datafiles larger than 3995m.
Tablespace extensions are expensive (disk hits). You try to size the
tablespace so that there is minimal extension (but it will happen). It
it needs to extent, it will and Oracle will have to "touch" each data
block of the new extent. This is costly.