Empty BLocks (Holes) in the Datafile

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 Received on Tue May 01 2007 - 10:00:06 CDT