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,
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;