Re: General SQL error / unable to extend index

Hi Rob -

Think you have 4 options here:

The first 3 add space available to your tablespace, the 4th is to drop the index.

1. Set autoexend ON, on your datafile for the tablespace DEVEL (assuming your devise is not full)
2. Increase the size of your datafile for the tablespace DEVEL ( " )
3. Add a new datafile for the tablespace DEVEL
4. Drop the index (assuming it is not part of a contraint definition in which case you'll need to drop the contraint)

nb. have a look at 'dba_free_space' for info. on free space for extents.

hope this helps

• Martyn Cavett DBA Invesco plc London, UK. home: cavett_at_globalnet.co.uk work: Martyn_Cavett_at_ldn.invesco.com

These views are my own and not those of my employer. Normal conditions apply.
nb. anonymous return address used - please replace reply address with those given above.

Rob Williamson wrote in message <36E0702F.873C1B9E_at_physics.umd.edu>...
>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 Sat Mar 06 1999 - 14:02:22 CST