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)
(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.
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)
Oh, and drop the now empty, but newly created "temporary" ts.
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...
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.
Click Here to Expand Forum to Full Width