Re: Empty BLocks (Holes) in the Datafile

On 5/2/07, Job Miller <jobmiller@xxxxxxxxx> wrote:


it looks like the image I sent didn't come through since it was a relative
graphic link to my computer, instead of the actual image.


did you try what I suggested?  It does exactly what you want I think..  It
shows you exactly where in the data file the holes are, and shows you the
segments all around the holes, so you know what if anything you need to move
to reclaim the space. (and it does that for you to).

the png image attached probably doesn't preserver the mouseover data
associated with each extent, but in DB Control that data is present as well.

it shows the block ids, the header blocks, the used, the unused, etc..


*BN <bnsarma@xxxxxxxxx>* wrote:



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


------------------------------
Ahhh...imagining that irresistible "new car" smell?
Check out new cars at Yahoo! 
Autos.<http://us.rd.yahoo.com/evt=48245/*http://autos.yahoo.com/new_cars.html;_ylc=X3oDMTE1YW1jcXJ2BF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDbmV3LWNhcnM->




Greetings,

I would like to, We dont have OEM Grid Installed, I have Oracle10g Client
Installed on my Laptop,
I dont see DBConsole,  Thats  (GC) another thing I would like to setup for
our projects.

Regards & thanks
BN


--
Regards & Thanks
BN

Other related posts: