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.