Table Fragmentation

The delete from the table deletes the rows but doesn't frees up the memory neither changes the high water mark.

The best way to get the memory cleared is truncating the table, but this hardly gets done in practical systems, and most of the time the delete and insert operations takes place.

So due to delete operations table gets fragmented, and in simple words its size appears more then the actual size of data it has. ( This will appear only if the compression is not enabled, check compression in dba_tables against the table).

Below query can help in identifying the tables which has different tablesize and actualsize, order by the maximum of difference.

set echo off

set lines 120

set pages 20

set pause on;

PROMPT ~ ~  Enter Full or part of Table Name/Owner, press enter for all ~ ~;

PROMPT ~ ~ Press Enter for next page, to move from pause; Cntrl^C to cancel ~ ~;

PROMPT ~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~;

select owner, table_name, round((blocks*8),2) table_size, round((num_rows*avg_row_len/1024),2) actual_size,

(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) as difference

from dba_tables where table_name like UPPER('%&Full_or_part_of_table_name%')

and owner like UPPER('%&Full_or_Part_of_owner_name%')

and blocks is not null

order by difference desc;

set pause off;

set echo on

If we find some tables are fragmented, we can do reorg for them.

The simplest way of table reorg is

SQL> alter table <Table_Name> move <TableSpace_Name>;

After reorg all the dependent indexes gets in 'UNUSABLE' state, so we have to rebuild them.

SQL> Alter index <Index_Name> rebuild [parallel] [Online];

Clause in [] are optional.

At the end we have to analyze table, which has many ways to do the same. One of them is

SQL> Analyze Table < Table_Name> [compute/estimate Statistics];