Fragmentation on tables

Hi guys, below is test case to determine if we have some fragmented object


## Step 1 ##

i'm creating the table and the index also (later for deleting purpose)
===============================================
SQL> select  /*+ full(a) parallel(a,8) */ count(*) from  TESTAB a;

  COUNT(*)
----------
   5247350

SQL> create unique index TESTAB_pk on TESTAB(ACCOUNT_ID);

Index created.


below is the estimate space before we gather the stat
=====================================================
SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
2    from dba_tables where table_name='TESTAB'
3    and owner  in ('TESUSER') ; 

TABLE_NAME    TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------- ----------------- -------------
TESTAB


SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2    from dba_segments
3    where segment_name='TESTAB'
4    and owner in ('TESUSER') group by  owner, segment_name;

OWNER         SEGMENT_NAME                 MB
------------- -------------------- ----------
TESUSER       TESTAB                   872




## Step 2 ##
Gather the stat for the table
=============================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 5);

PL/SQL procedure successfully completed.


SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner  in ('TESUSER') ;   2    3

TABLE_NAME        TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
----------------- ----------------- -------------
TESTAB                    866.88        725.71


SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by  owner, segment_name;  2    3    4

OWNER          SEGMENT_NAME             MB
-------------- ---------------- ----------
TESUSER        TESTAB               872



## Step 3 ##
==============================================
deleting tables to produce the fragmented space
==============================================

SQL> create table TESTAB_accid as select ACCOUNT_ID from TESTAB where rownum < = 2000000;

SQL> delete from TESTAB
where ACCOUNT_ID in ( select ACCOUNT_ID from TESTAB_accid );

=================
Gather stat again
=================
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner  in ('TESUSER') ;  2    3

TABLE_NAME                     TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB                                 866.88        445.96

SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
2    from dba_segments
3    where segment_name='TESTAB'
4    and owner in ('TESUSER') group by  owner, segment_name;

OWNER         SEGMENT_NAME   MB
------------- ------------ --------
TESUSER       TESTAB         872



## Step 3 ##
==============================================
Defrag table and don't forget to rebuild index
==============================================
SQL> alter table TESTAB move tablespace datal01;

Table altered.

SQL> alter index TESTAB_PK rebuild;

Index altered.


SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TESUSER', TABNAME => 'TESTAB', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 2', DEGREE => 12,CASCADE => TRUE,estimate_percent => 10);

PL/SQL procedure successfully completed.

SQL> select table_name, round((blocks*8)/1024,2) tblsz_frgmnted_MB, round((NUM_ROWS*avg_row_len/1024/1024),2) actualsize_MB
from dba_tables where table_name='TESTAB'
and owner  in ('TESUSER') ;   2    3


as shown below the gap still there because of from the actual size dont include for some percentage each block reserve for header, dict, etc

TABLE_NAME                     TBLSZ_FRGMNTED_MB ACTUALSIZE_MB
------------------------------ ----------------- -------------
TESTAB                                 531.92        447.49

SQL>
SQL> select owner, segment_name , sum(bytes/1024/1024) as MB
from dba_segments
where segment_name='TESTAB'
and owner in ('TESUSER') group by  owner, segment_name;   2    3    4

OWNER       SEGMENT_NAME       MB
----------- -------------- ----------
TESUSER     TESTAB             536