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,
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
Try 'alter tablespace xxxxx coalesce ; as temporary solution.
And rebuild your indexes using the following dynamic sql script:
select 'alter index '||owner||'.'||segment_name||' rebuild;'
where owner not in ('SYS','SYSTEM','DBSNMP')
-- where owner in ('TCOOLS','C_AFFAIRS')
and segment_type='INDEX' and extents > 2;
I don't think you can use the "REBUILD" option in 7.1.x. You may be relegated to drop/create statements...
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]
Click Here to Expand Forum to Full Width