Oracle Database Table Fragmentation:

In oracle schema sometimes found in some tables having big difference in actual size (from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)).This happens due to fragmentation in the table or stats for table are not updated into user_tables.

If only insert happens in a table then there will not be any fragmentation.Fragmentation happens when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get  reuse ever at all). This leaves behind holes in table which results in table fragmentation.

how oracle manages space for tables.

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.(High Water Mark).

When rows are inserted into the table, the high watermark(used blocks) of the table moved forward in order to accommodate new rows into the table. But during delete operation, oracle doesn’t allow high watermark to shift backward in order to decrease table size and release the free space.Ideally once we delete the data from the table, the free space should be released or reused but additionally oracle acquire new blocks to accommodate the new rows for insertion which causes hole into the table.

Table fragmentation, which cause slowness and a wastage of space.
Fragmentation is a common issue in oracle database which occurs due to excessive dml operations like insert followed by update and delete operations.


Gather table statistics:

In order to check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

To fins LAST_ANALYZED:
select table_name,last_analyzed,stale_stats from user_tab_statistics where table_name='&TABLE_NAME';

To find Table size:
select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='&TABLE_NAME';

EXEC dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME', method_opt=> 'for all indexed columns size skewonly', granularity => 'ALL', degree => 8 ,cascade => true,estimate_percent => 15);

To find actual table size, fragmented size and percentage of fragmentation in a table.:

set pages 50000 lines 32767;
select owner,
       table_name,
       round((blocks * 8), 2) || 'kb' "Fragmented size",
       round((num_rows * avg_row_len / 1024), 2) || 'kb' "Actual size",
       round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2) || 'kb',
       ((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) /
       round((blocks * 8), 2)) * 100 - 10 "reclaimable space % "
  from dba_tables
 where table_name = '&table_Name'
   AND OWNER LIKE '&schema_name';
=========== 
 
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';

***If you find more than 20% fragmentation then you can proceed for de-fragmentation.

To find Top 10 fragmentation tables:
select *
      from (select table_name,
               round((blocks * 8), 2) "size (kb)",
               round((num_rows * avg_row_len / 1024), 2) "actual_data (kb)",
               (round((blocks * 8), 2) -
               round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)"
          from dba_tables
         where (round((blocks * 8), 2) >
               round((num_rows * avg_row_len / 1024), 2))
         order by 4 desc)
 WHERE ROWNUM <= 10;



To find indexes on the table:
select index_name from dba_indexes where table_name='&TABLE_NAME';

To reset HWM / remove fragmentation:

There are three way to do this:

1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-(Depends upon the free space available in the tablespace) 
2. Export and import the table:- (difficult to implement in production environment)
3. Shrink command (from Oracle 10g onwards)(Shrink command is only applicable for tables which are tablespace with auto segment space management)

Way1:

select index_name,status from dba_indexes  where table_name like '&table_name';

For same tablespace:

alter table <table_name> move;

For  new tablespace:

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;

rebuild all the indexes:
alter index <INDEX_NAME> rebuild online;

Way2: 
Export and import the table.

Way3:
Shrink command:
Its in introduced in 10g,applicable for tables which are tablespace with auto segment space management.It is online automatic segment space.

row movement should be  enabled.
alter table <table_name> enable row movement;

alter table <table_name> shrink space compact; >>In this Rearrange rows and then reset HWM,All DML's will happen during this time.

alter table <table_name> shrink space;>>In Reset HWM, No DML will happen,its very quick

After completing these steps, table statistics must be gathered.

Oracle function-based index:

We will give demonstration with table name department created in the CREATE INDEX. 

Statement to creates an index on the first_name column of the department table:

CREATE INDEX department_i ON members(first_name);

if in WHERE clause first_name column is being used ,optimizer will definitely use the index:

select * from  department  WHERE first_name = 'Man';

if function is being used on the indexed column first_name as below then optimizer will not use index.

select * from  department  WHERE UPPER(first_name) = 'MAN';

To overcome above concern, Oracle introduced function-based indexes:

A function-based index calculates the result of a function that involves one or more columns and stores that result in the index.

syntax of creating a function-based index:

CREATE INDEX index_name ON table_name (expression)

index expression can be an arithmetic expression or an expression that contains a function such as a SQL function, PL/SQL function, and package function.

*** function-based index can be a btree or bitmap index.
example:

CREATE INDEX department_i ON members(UPPER(first_name));

Here Oracle will convert all values of first_name column to uppercase and stored  in index department_i .

Now below quey will use index to fetch the data.

select * from  department  WHERE UPPER(first_name) = 'MAN';

Oracle Rebuilding Indexes:

Oracle doc id:1373415.1