Check fragmentation of table

--check fragmentation of table ( > 100MB)
--Please note that the script is useful only if the tables involved have statistics gathered on them
set lines 300;
column owner format a15;
column segment_name format a35;

select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1048576,0) in_MB,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1048576,0) WASTED_MB
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
--and a.owner like 'APPOWNER'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by
a.owner, a.segment_name, a.segment_type,
round(a.bytes/1048576,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1048576,0)
having round(bytes/1048576,0)  > 100
order by round(bytes/1048576,0) desc ;

--to gather stats for schema

EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'ADMKSK', ESTIMATE_PERCENT=>100, CASCADE => TRUE, degree => 4 );

--to check the completion of stats on all the objects of the schema
select min(LAST_ANALYZED) from dba_tables
where owner='ADMKSK';


select min(LAST_ANALYZED) from dba_indexes
where owner='ADMKSK';

-- Rebuilding the table by shrinking its free space in 12c
alter table ADMKSK.&tab1 move online update indexes;

-- only for a specific index
alter index ADMKSK.&ind1 rebuild online;

--check after the rebuild
column owner format a15; 
column TABLE_NAME format a35; 
column index_name format a35; 
select TABLE_NAME,index_name,status from dba_indexes
where owner='ADMKSK'

and status !='VALID';

-- Rebuilding the table by shrinking its free space before 12c
Starting with Oracle 10g  'SHRINK SPACE' can be used to get tables reorganized online, without affecting the users functionality. 

That is: 

* The table itself must be in an ASMM tablespace 
* The table must have row movement enabled 
* At the end of the action a short table level lock takes place to adjust the HWM (Highwater Mark) of the table. 

And it's easy !! 
Just enable 'row movement' and start shrinking tables: 

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT; 
This enables row movement of the table. This is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed. 

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT; 
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don't want to have a table-level lock during business hours. 

SQL> ALTER TABLE MYTABLE SHRINK SPACE; 
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM. 

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE; 
This command shrinks the contents of the table and all dependent objects like indexes. 

credits to : http://managingoracle.blogspot.sg/2010/08/oracle-table-reorganization-online-or.html

De-fragmentation of Indexes

1 - ALTER INDEX COALESCE

2 - ALTER INDEX SHRINK SPACE

3 - ALTER INDEX SHRINK SPACE COMPACT

4 - ALTER INDEX REBUILD

Options 1 and 3 do NOT free up blocks. They just free up space in existing blocks. 

Coalesce does a little bit worse job, there will be more blocks with only 25-50% free space, while with shrink space compact, there will be more blocks with 75-100% free space. The total number of blocks, however, stay the same. For example, an index with 200 blocks with coalesce, and after deleting 1/5 of the rows randomly, will have ~1/5 of the index blocks have 25-50% free space while the rest remain full.

On the other hand, shrink space and rebuild do free up the blocks, and merge them into existing ones, thus reducing the total number of blocks. I think the only difference is speed. 

When you delete only 5% from a large table, there's no reason to rebuild the entire index, and it will be very slow. However, shrink space might be a little bit faster here, because it does not rebuild the entire index, just reorganizes the blocks.

Obviously the fastest choice would be coalescing or shrinking space with compact option.

--For rebuilding partitions, refer the below link
http://dbakumar.blogspot.sg/p/local-indexes-for-partitioning-select-i.html