Solved

script for checking empty block by segment

Posted on 2011-02-16
3
646 Views
Last Modified: 2012-05-11
Hello,

I search a script for checking empty block by segment.

Thanks

Regards

bibi
0
Comment
Question by:bibi92
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34912380
0
 
LVL 5

Expert Comment

by:manzoor_dba
ID: 34913206
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
 

Author Closing Comment

by:bibi92
ID: 34914200
Thanks bibi
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question