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

Thread: creating free space in INDX tablespace

  1. #1
    Join Date
    May 2001
    Posts
    39

    Smile

    sir,
    In my production database,whenever i try to rebuild an index it gives me error not enough free space to rebuild
    .It also shows some 3540 chunks in the INDX tablespace;
    also when i try to COALESCE the tablespace their is no
    outcome.
    Pls give me inputs on this matter to resolve this issue
    of the defragmentation and releasing free space
    in INDX tablespace.


    Sanctus
    sanctus

  2. #2
    Try dropping all indexes, "coalesce"ing the tablespace and then recreating all the indexes, that way you will remove defragmentation! This was the way I used to handle this problem, probably someone else will have a better solution to this!

    Thanks,
    -S

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    It might be impossible to drop all indexes in production database.
    Add more space to the INDX tablespace with setting any of its datafiles to AUTOEXTEND or resize it manually with ALTER DATABASE DATAFILE ... RESIZE.
    If you need to rebuild all indexes, start with the largest one to create space in tablespace and then rebuild the others, coalesce space after each index.
    You could also try to not add space in the tablespace and start with the smallest one, it's up to you.

    If you have Enterprise you can use REBUILD ONLINE which allows users to update the index during rebuild, otherwise the index is locked.

    HTH,
    Ales


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