Don't start recreating the database with a bigger block size without very
good reason. It may have knock on effects you don't cater for.
Safer just to recreate the index and size it for the future or have
autoextend on.
manager43_at_my-dejanews.com wrote in message
<7btivt$jnh$1_at_nnrp1.dejanews.com>...
>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 Mon Mar 08 1999 - 03:08:16 CST
