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:
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
Other related posts:
