Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

script for checking empty block by segment

Hello,

I search a script for checking empty block by segment.

Thanks

Regards

bibi
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of manzoor_dba
manzoor_dba

Hi,

Hope the below example will help..

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> select blocks user_Tables where table_name = 'EMP';

    BLOCKS
     ---------
     26111  

It is showing that currently 26111 blocks has been used by this table, but there may be lots of empty blocks in it ( due to DML Operation) , so we can query the below to find out exactly how many used blocks are there in this table.

SQL> select count( distinct dbms_rowid.ROWID_BLOCK_NUMBER(rowid))  "Actual Blocks" from emp;

ACTUAL BLOCKS
-----------------------
                    20363

it shows that 20363 blocks contains data which means   26111 - 20363 = 5748 , there are totally 5748 empty blocks are there in the emp table.

Thanks...
Avatar of bibi92

ASKER

Thanks bibi