Unable to extend: Urgent

Printable View

03-29-2001, 10:44 AM
badrinathn

Hi,
Yesterday, I had a problem as
'Unable to extend Index REI.USAGE_ACCOUNT_ID_IDX' by 65536 in tablespace REI_PROD_INDEX.

So I added another datafile of 500M to rei_prod_index tablespace.
But still I am getting the same problem.

Should I extend the extents only to the REI.USAGE_ACCOUNT_ID_IDX only. Please help

Badrinath
03-29-2001, 10:47 AM
saiprasadr

check with user qouta wherther it exceeded or not
03-29-2001, 10:59 AM
badrinathn

I checked up in my dba_indexes. For Usage_account_id_idx, (on the table REI_USAGE_HISTORY), the Initial extents is

536870912 and Next is 536870912 and the max extents is 100.

What to do next?
Badrinath
03-29-2001, 11:06 AM
pando

according your storage parameters, you need 512MB not 500MB, that´s the problem

hth
03-29-2001, 11:08 AM
badrinathn

On the table REI_USAGE_HISTORY, I have an index 'USAGE_ACCOUNT_ID_IDX', for which the initial and next are
536870912. As I got this error ORA-01654, I added another 500M only. Is this small for extending the next extent.

Badrinath
03-29-2001, 11:10 AM
badrinathn

So, can I add another datafile which is more than 12MB, and the extent should be created...Is that right.
Badrinath
03-29-2001, 11:16 AM
badrinathn

As the error say 'Unable to extend index REI.Usage_account_id_idx' by 65536.

What does it mean by saying '65536' while my next extent size is different (512M).

Badrinath
03-29-2001, 11:55 AM
sreddy

There is possibility that index has object level storage definition and you are talking about tablespace level storage clause(512M).
03-29-2001, 04:16 PM
jmodic

Quote:

Originally posted by badrinathn
As the error say 'Unable to extend index REI.Usage_account_id_idx' by 65536.

What does it mean by saying '65536' while my next extent size is different (512M).

Badrinath

It says it needs 65536 *contiguous* db blocks in that tablespace to create next extent for your index. If you multiply this number of blocks with your block size (which obviously is 8K) you'll get exactly 512MB. So your datafile of 500MB can not be used for that extent, it is too small. Even adding another smaller datafile will not help, as the blocks in those two files will not be contiguous and an extent can not span across different datafiles.

So you only have two options:
a) enlarge your 500M datafile or create a new one of sufficient size OR
b) modify the NEXT storage parameter of your index (make it smaller)