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
