Re: Empty BLocks (Holes) in the Datafile

On 5/1/07, rjamya <rjamya_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l