Re: Empty BLocks (Holes) in the Datafile

  • From: BN <bnsarma@xxxxxxxxx>
  • To: rjamya <rjamya@xxxxxxxxx>
  • Date: Tue, 1 May 2007 21:45:24 -0400

On 5/1/07, rjamya <rjamya@xxxxxxxxx> wrote:


you could write a creative query against dba_extents using header_block
and number of blocks for each extent of each segment and see for yourself
where the holes might be.

1. are you worried that since you don't seet he holes, there aren't any?
2. you need that visual gratification of seeing free space?



I was hoping to see all the (once) formatted Blocks from dba_extents and if
I can visually see the block numbers (not like Microsoft Graphics) and if I
can verify/confirm    holes,  I feel I am in the right track,
If not I need to understand what I am missing.

This Tablespace/Datafile is converted to LMTS from dictionary during 10g
upgrade Process,  I am thinking there are holes.

Outof 6.9 GB only 1.2 GB is  used, I know I cannot resize it ( Still I
tried an got the error message),
So there is a Hole,  not sure where it is, Curious to see where it is, from
what block number to what block number.

I know the last segment in this datafile (XPKSPECIALDELPHICODEDETAILS) is an
Index (PK)
How can I be Sure that  if  I  move/rebuild this Index to a different TBS,
Will I get back all the space.

I am  willing to do a re-org (exp/imp, datapump..) but want to understand
what  am I missing.

I  wrote this script to see and understand, the block map (and missing
blocks) but not sure  if this is the right one. I appreciate if any of you
can check /correct this one to help me understand what  am I missing in my

My Goal for this script is to see the Allocated and Missing Block (holes)

max(BLOCK_ID),BLOCKS,(BLOCKS+max(BLOCK_ID)) "Actual_Next",
 lead(max(BLOCK_ID)) over(order by BLOCK_ID) Real_Next
  from dba_extents where FILE_ID=18
 group by

I highly  Appreciate your help in this.

Regards & Thanks

Other related posts: