Oracle table fragmentation how to calculate or get the actual used blocks of the table

Oracle table fragmentation how to calculate or get the actual used blocks of the table

from:http://www.almohem.com/oracle/space-managment/oracle-table-fragmentation-how-to-calculate-or-get-the-actual-used-blocks-of-the-table/

Oracle table fragmentation, how to calculate used block of the table ?

---

Ans:

1. To get the total space allocated to the table (this includes : used blocks+fragmentation) you can select bytes
from dba_segments :

SQL> select bytes/1024/1024
from dba_segments where segment_name = 'EI_AIR_REQ_LOG';

BYTES/1024/1024
---------------
            472

2. Select number of rows :

SQL> select count(*) from M_EAI_WMPROD.EI_AIR_REQ_LOG;

  COUNT(*)
----------
    300094

3. Select actual blocks used by the this table :
SQL>  SELECT
COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid))
"Used" from M_EAI_WMPROD.EI_AIR_REQ_LOG;

      Used
----------
     60021

4. Translate the block number to mega bytes (block size = 8192 byte) as follow
SQL> select 60021*8192/1024/1024 "MB Used" from dual;

   MB Used
----------
468.914063

SQL>

As you can see from this example this table does not contains much fragmentation and hence does not require storage reorganization.