Rebuild indexes

[sonaliak]

Can someone help me with the script to rebuild indexes ?
I have oracle 8.0.6 and oracle 8i both. I want to rebuild indexes for both databases.

My system tablespace is highly fragmented. How do I defragement it ?
How do I defragement user tablespace too ?

My email is:
sonaliak@hotmail.com
Thanks
Sonali

[maachan]

ALTER INDEX ____.____ REBUILD TABLESPACE _____;

You can run a script like:
SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE _____;'
FROM DBA_INDEXES
WHERE _______;

with the proper WHERE clause for the indexes in need of rebuilding. If they are in the proper tablespace, you don't need the rebuild clause. Then spool the output and run the spooled file.

The best way to defragment a tablespace is to export/import.
However, before you do this, it is important to analyze what caused the fragmentation in the first place, so that you can avoid further fragmentation in the future.

Good luck

[pando]

you cant defragment system tablespace... not even with EXP/IMP