Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Empty BLocks (Holes) in the Datafile

Re: Empty BLocks (Holes) in the Datafile

From: BN <bnsarma_at_gmail.com>
Date: Wed, 2 May 2007 08:53:53 -0400
Message-ID: <61292a9d0705020553k2c34b6f0i7dca21e9b480f26c@mail.gmail.com>


On 5/2/07, Job Miller <jobmiller_at_yahoo.com> 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_at_gmail.com>* wrote:
>
>
>
> 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
>
>
> ------------------------------
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed May 02 2007 - 07:53:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US