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.
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).
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)
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?