fragmentation

[jn1971]

Hi guys,
I just find out that some of my tablespaces are dangerously fragmented @98%. Please tell me how i can defrag or fix the problem. They are IDX and TEMP tablespace. oracle7.1.4 on unix.
Thanks in advance,

:(

[Kishore]

Hi You can rebuild the index's by dropping them. while rebuilding you may want to create drop the tablespace too and create back with new storage parameters.

I think for Temp Tablespace you need not worry

[sreddy]

Try 'alter tablespace xxxxx coalesce ; as temporary solution.
And rebuild your indexes using the following dynamic sql script:


spool rbuildindx.sql
select 'alter index '||owner||'.'||segment_name||' rebuild;'
from dba_segments
where owner not in ('SYS','SYSTEM','DBSNMP')
-- where owner in ('TCOOLS','C_AFFAIRS')
and segment_type='INDEX' and extents > 2;
spool off

[marist89]

I don't think you can use the "REBUILD" option in 7.1.x. You may be relegated to drop/create statements...

[sreddy]

You can use 'REBUILD' option on v7.3, I just looked into Manual. Not sure about lower versions...

Yes, Jeff is Right! You can't use 'REBUILD' on lower versions of 7.3. Its valid from 7.3 onwards. I checked it up with 7.2 manual... there is no rebuild option.

[Edited by sreddy on 12-28-2000 at 05:24 PM]