I was searching long time ago for some fragmentation scripts,and i found many in Metalink,but what i am asking about is i got scripts to show me tables fragmentation,but how to solve it?
it supposed by Smon automatically.but is'n there any manual method?

فين الشباب؟
----------------------------------------------------------------------------------------------------
واضح انه مستعصى

تم تعديل 13 أبريل 200619 سنة بواسطة Debug_b!bo

Ok Shabbab i got a solution but i think it is not so cool
1-move objects from table space
2-coalesce it
3-move them back and rebuild indexes
try this script on 9i or above:
select tablespace_name,
100-(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks))))) fragmentation
from dba_free_space where fragmentation != 0
group by tablespace_name
order by 1
/

This SQL script will generate report about table's fragmentation. The script requires the table statistics to be gathered recently.

set pages 9999 
set echo off 
set heading off 
set feedback off 
REM create temp table to store query results 
REM 

create table temp_frag_table 
( table_name varchar2(30), 
hwater_blocks number, 
used_blocks number 
); 

REM 
REM first select is for non-partitioned tables 
REM second select is for partitioned tables 
REM 
spool tmpfrag.sql 

SELECT 
'INSERT INTO temp_frag_table '||chr(10)|| 
'select '''||table_name||''','||chr(10)|| 
' ( s.blocks - (t.empty_blocks - 1)) , '||chr(10)|| 
' ( t.blocks ) '||chr(10)|| 
'from user_tables t, user_segments s'||chr(10)|| 
'where t.table_name = s.segment_name'||chr(10)|| 
' and s.segment_type = ''TABLE'''||chr(10)|| 
' and t.partitioned = ''NO'''||chr(10)|| 
' and t.table_name = '''||table_name||''';' 
from user_tables; 

SELECT 
'INSERT INTO temp_frag_table '||chr(10)|| 
'select '''||partition_name||''','||chr(10)|| 
' ( s.blocks - (t.empty_blocks - 1)) , '||chr(10)|| 
' ( t.blocks ) '||chr(10)|| 
'from user_tab_partitions t, user_segments s'||chr(10)|| 
'where t.table_name = s.segment_name'||chr(10)|| 
' and t.partition_name = s.partition_name'||chr(10)|| 
' and s.segment_type = ''TABLE PARTITION'''||chr(10)|| 
' and t.partition_name = '''||partition_name||''';' 
from user_tab_partitions; 

spool off 
start tmpfrag 
REM host rm tmpfrag.sql 

REM 
SET VERIFY ON; 
SET HEADING ON; 
SET LINESIZE 78; 
SET PAGESIZE 64; 
COLUMN USED_BLOCKS FORMAT 99999999 
COLUMN HWATER_BLOCKS FORMAT 99999999 

spool fragtables.out 
prompt --------------------------------------------------------------------; 
prompt USED_BLOCKS = number blocks in table that contain data 
prompt HWATER_BLOCKS = total blocks below table highwater mark 
prompt OMEGA1 = proportion of blocks up to highwater containing no rows 
prompt WASTE SCANS = number of blocks scanned containing no rows 
prompt -------------------------------------------------------------------- 
prompt 

REM 
SELECT to_char(sysdate,'DD MON YYYY HH24:MI')||' -- '||user||' -- '||substr(global_name,1,instr(global_name,'.')-1) "REPORT RUN..." 
FROM global_name; 
REM 
REM 
select table_name, 
used_blocks used_blks, 
hwater_blocks hwater_blks, 
round(1-(used_blocks/hwater_blocks),2) "OMEGA1" , 
hwater_blocks - used_blocks "WASTE SCANS" 
from temp_frag_table 
where table_name != 'TEMP_FRAG_TABLE'; 

drop table temp_frag_table; 

spool off 

Dear Admin
thnx alot for ur fast response, i've read in Metalink for some scripts like that but the main isse is how to solve this fragmentation
Regardz