Fragmentation, Index rebuild, Gather stats

Some of the scripts used by dba for fragmentation, index rebuild, gather stats.

select owner,index_name,table_owner,table_name,status from dba_indexes where status='unusable';

Fragmentation

select 'alter table '||owner||'.'||segment_name||' move tablespace '||tablespace_name||';' from dba_segments
where owner='schema_name' and segment_name in('table_name');

Rebuild index

select 'alter index '||index_owner||'.'||index_name||' rebuild online;' from dba_ind_columns
where table_owner='schema_name' and table_name in('table_name');

Gather stats

exec dbms_stats.gather_schema_stats (ownname => 'schema_name', cascade =>true, estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => 14)

exec dbms_stats.gather_schema_stats (ownname => 'schema_name', cascade =>true, estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size auto', degree => 14)

Update:

With standard Oracle tables, we can reclaim space with the "alter table shrink space" command:
SQL> alter table enable row movement;


SQL> alter table shrink space;


SQL> Alter table shrink space cascade;