Re-Organizing Tables in Oracle Database

Hi All,
Here is the way you can find scripts to re-Organize your Fragmented tables in oracle database

Fragmented Tables Script:
-------------------------
select
t.owner ,t.table_name,t.num_rows,t.tablespace_name,
round((t.num_rows*t.avg_row_len/1024)) est_could_be_kb,s.bytes/1024 curr_kb,
round((s.bytes-(t.num_rows*t.avg_row_len))/1024) est_wasted_kb from dba_tables t,dba_segments s
where t.table_name=s.segment_name and t.owner = s.owner and t.owner not in ('SYS','SYSTEM')
and round((s.bytes-(t.num_rows*t.avg_row_len))/1024)>0.001*1024*1024
 and t.num_rows!=0
order by 7 asc;

Table Moving Script:
-------------------
select 'alter table '||t.owner||'.'||t.table_name||' move;' from dba_tables t,dba_segments s
where t.table_name=s.segment_name and t.owner = s.owner and t.owner not in ('SYS','SYSTEM')
and round((s.bytes-(t.num_rows*t.avg_row_len))/1024)>0.001*1024*1024
 and t.num_rows!=0


Index Rebuilding Scripts:
-------------------------
select owner,index_name,degree from dba_indexes where owner not in('SYSTEM','SYS') and status like 'UNUSABLE';

select 'alter '||'index '||owner||'.'||index_name ||' rebuild nologging parallel 22'||';' from dba_indexes where
owner not in ('SYS','SYSTEM') and status like 'UNUSABLE';

select 'alter '||'index '||owner||'.'||index_name ||' logging parallel 1'||';' from dba_indexes where owner not
in ('SYS','SYSTEM') and status like 'UNUSABLE';

Gather Stats:
-------------
select 'Execute dbms_stats.gather_schema_stats(ownname=>'''||username||''',cascade=>True);'
from dba_users where username not in ('SYS','SYSTEM');