Unable to extend: Urgent

[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

[saiprasadr]

check with user qouta wherther it exceeded or not

[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

[pando]

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

hth

[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

[badrinathn]

So, can I add another datafile which is more than 12MB, and the extent should be created...Is that right.
Badrinath

[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

[sreddy]

There is possibility that index has object level storage definition and you are talking about tablespace level storage clause(512M).

[jmodic]

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)