Re: Empty BLocks (Holes) in the Datafile

bnsarma_at_gmail.com wrote:
> Greetings
>
> Oracle 10gRel2, on HP-UX
>
> The Db (550GB) is migrated from 8.1.7.4 to 10gRel2, a year back.
> During Migration we have CONVERTED Dictionary TBS to LMTS using
> "dbms_space_admin.tablespace_migrate_to_local"
>
> We have Deleted around 190 B of Tables and its Indexes that Belongs
> to 4 different LMTS Tablespaces spread across in total 61 datafiles
> (for all the 4 TBS)
>
> For Some reason We are not able to see the space We deleted in these
> Datafiles,
> I have PURGED the Recycle Bin, no Luck.
>
> My Goal is to reclaim the space.
>
> Please Note We are not using ASSM, so I am guessing SHRINK is out of
> the question
>
> I am thinking, since these are converted LMTS, there may be still big
> holes in the datafiles thats holding the space.
>
> How can I see these holes (number of Blocks), I tried dba_extents
> Lead Block - Blocks they look
> contiguous, Unless I made a mistake in the SQ L.
>
>
> Appreciate if any of you can throw more light and share a SQL to
> see where these holes are located in the datafile, so that If they
> are at the end (almost) I can move a few segmetns to release the space
> to resize the datafiles
>
> Regards & thanks
> BN

You give no indication of what you are looking at.

Why write "Unless I made a mistake in the SQL" and not post is so we can see?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org