Coalescing an Index Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Coalescing an Index Tablespace

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    I've an Index TBS with 5 x 100Mb datafiles.

    Over the past year i've regularly rebuilt indexes as and when they've extended and I'm now in the position where each datafile is c. 80% full. (I presume this is because of the regular rebuilds)

    Could I

    (a) REBUILD the indexes to a separate temporary tablespace with 1 500 Mb datafile.
    (b) Coalesce my existing,now empty, original index tablespace.
    (c) Drop 1 of my 5 index datafiles.
    (d) REBUILD the undexes back into the newley coalesced TBS with 4 x 100 Mb datafiles.

    ?




  2. #2
    Join Date
    Jul 2000
    Posts
    37
    Hi,

    If i were you, I'd create a new ts with 1 4-500 mb datafile,
    alter index index_name rebuild tablespace new_ts_name
    /
    drop your original index tablespace

    Recreate a new one with say 1 4-500mb datafile

    alter index index_name rebuild tablespace orig_ts_name
    /
    (to move them back to your original but newly re-created ts)

    done.

    Oh, and drop the now empty, but newly created "temporary" ts.

    cheers

    chris.

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    pedantic point or????

    Any 'advantage' in using 1 x 400 Mb file over using 4 x 100Mb files?

    If no, is there any problem in me creating the 4 x 100Mb files and rebuilding the indexes into them?

    All my datafiles in the DB are 100Mb and its easy for me to 'see' how the TBS grows if I see individual 100Mb datafiles...

  4. #4
    Join Date
    Jun 2002
    Posts
    15
    More files in a tablespace can also help you to avoid OS hot file issues and hot areas on a file system / device... stripe or not to stripe another topic to discuss.

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