General SQL error / unable to extend index

Does any one know the best way to fix

General SQL error ORA-01654 : unable to extend index MYINDEX by 15 in tablespace DEVEL

using some validate index commands we have decided that the index has not
reached more than 1% of dead entries.

using the dba_tablespaces table I found hte following

initial_extent = 10240, next_extent = 10240, min_extents = 2 max_extents = 121, pct_increase = 50, status = online, contents = permanent

using dba_extents table for tablespace_name = devel I found:

3 records returned with all the same owner and segment_name ( I_specordpk)
and type (index) and table space = devel and file_ID = 7

the other things were not the same

block_id = 257, bytes= 20480, blocks=10 extent_ID= 2
block_id = 217, bytes= 10240, blocks=5 extent_ID= 0
block_id = 222, bytes= 10240, blocks=5 extent_ID= 1

• when I looked at just the tabl_space names there were other segment names in the table but there were only 5 extent_id's used for all 56 entries ( 0-5)

the dba_segments table for the segment name I_specordpk returned this: header file = 7, header block = 217, bytes = 40960, blocks=20, extents= 3,
initial_extent = 10240, next extent = 30720, min extents = 2, max extents = 121,
pct_increase= 50, freelists = 1 freelist_groups = 1

the dba_data_files table rturned:
file_name= develind.dbf
file_id = 7, table_space = devel, bytes = 1024000, blocks = 500, status= available

when I looked at that file in UNIX develind.dbf had 1026048 bytes

This last thing looks like the problem but I am not sure how to move from here.

Oracles solution under error ORA-01654 says to use ALTER TABLESPACE ADD DATAFILE to add one or more files to the specified tablespace.

One book I have shows an example of ALTER DATAFILE 'develind' RESIZE 10M;

Can this be done?
Another tip in the Tuning book says to Use the ALTER TABLE ALLOCATE EXTENT; I'm not sure what all this means yet but I thought I would send this out so I might
learn more over the weekend.

I also understand I can just drop the index and I will only slow things down.

Which may be a solution because this software and DB are slated to be replaced in the next
3 months.

Any Help would be appreciated

Thanks
Rob Received on Fri Mar 05 1999 - 18:00:47 CST