script for checking empty block by segment

Hello,

I search a script for checking empty block by segment.

Thanks

Regards

bibi
bibi92Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
0
 
manzoor_dbaCommented:
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...
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.