Re: General SQL error / unable to extend index

Hi,

Recreate (!) database with another database block size -

2K - 121 extents;
4K - 249 extents;
etc.

Vladimir

In article <36E0702F.873C1B9E_at_physics.umd.edu>,   Rob Williamson <robw_at_physics.umd.edu> wrote:
> 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
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Mar 07 1999 - 04:04:46 CST