DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Unable to extend: Urgent

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    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

  2. #2
    Join Date
    Mar 2001
    Posts
    3
    check with user qouta wherther it exceeded or not



  3. #3
    Join Date
    Jan 2001
    Posts
    642
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    according your storage parameters, you need 512MB not 500MB, that´s the problem

    hth

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    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

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    So, can I add another datafile which is more than 12MB, and the extent should be created...Is that right.
    Badrinath

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    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

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    There is possibility that index has object level storage definition and you are talking about tablespace level storage clause(512M).
    Reddy,Sam

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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)
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width