Re: Empty BLocks (Holes) in the Datafile
- From: rjamya <rjamya@xxxxxxxxx>
- To: BN <bnsarma@xxxxxxxxx>
- Date: Wed, 2 May 2007 07:25:01 -0400
try
column sb heading "Start Block"
column eb heading "End Block"
column nsb heading "Next Ext Start Block"
with x as (select file_id, block_id sb, block_id+blocks-1 eb, lead(block_id,
1) over(partition by file_id order by block_id) nsb
from dba_extents where tablespace_name = 'SYSAUX' order by 1,2,3)
select file_id, sb, eb, nsb, case when (nsb-eb) > 1 then 'HOLE - ' ||
(nsb-eb) || ' blocks' else 'OK' end stat
from x
/
This will show you holes, you can add segment names by modifying the query
if you like.
HTH & YMMV
rjamya
Other related posts:
