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.
1700

被折叠的 条评论
为什么被折叠?



