FreeLists / oracle-l / Re: Empty BLocks (Holes) in the Datafile

[BN <bnsarma@xxxxxxxxx>]

On 5/1/07, rjamya <rjamya@xxxxxxxxx> wrote:
> BN,
>
> 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?
> or
> 2. you need that visual gratification of seeing free space?
>
> rjamya
Greetings

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

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

select SEGMENT_NAME,SEGMENT_TYPE,
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
BN