Re: Empty BLocks (Holes) in the Datafile

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 01 May 2007 14:05:00 -0700
Message-ID: <1178053498.694308@bubbleator.drizzle.com>

bnsarma_at_gmail.com wrote:
> On May 1, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:

>> bnsa..._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
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
>
> Greetings
>
> Sorry Daniel, for not giving all the info:
>
> Free Space I see in these TBS that were part of the TABLE/Index
> Drops
>
> TBS GB USED FREEGB PCT_USED pct_free
> LARGEST
> ------------ ---------- ---------- ---------- ---------- ----------
> ----------
> AS_INDEX_ESL 1.26855469 .01953125 1.24890137 1.54926867 98.5
> 773.125
> AS_INDEX_S 26.7001953 3.57421875 23.1259766 13.3864892
> 86.6 452
> AS_INDEX_ES 13.3974609 2.66796875 10.7294083 19.9146143 80.1
> 58.28125
> AS_DATA_ES 72.8701172 15.1992188 57.6711502 20.8576129
> 79.1 496
> AS_DATA_L 94.8652344 45.6962891 49.1688538 48.1697862
> 51.8 496
> ---------- ---------- ----------
> sum 209.101563 67.1572266 141.94429
>
>
> SQL I used:
>
> select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKOWN')) TBS,
> mbytes_alloc/1024 GB,
> round(mbytes_alloc-nvl(mbytes_free,0))/1024 used,
> nvl(mbytes_free,0)/1024 freeGB,
> ((mbytes_alloc-nvl(mbytes_free,0))/ mbytes_alloc)*100 pct_used,
> round((mbytes_free/mbytes_alloc)*100,1) "pct_free",
> nvl(largest,0) largest
> from
> (select sum(bytes)/1024/1024 Mbytes_free, max(bytes)/1024/1024
> largest, tablespace_name
> from sys.dba_free_space
> where TABLESPACE_NAME in
> ( 'AS_DATA_L','AS_DATA_ES','AS_INDEX_S','AS_INDEX_ES','AS_INDEX_ESL')
> group by tablespace_name ) a,
> (select sum(bytes)/1024/1024 Mbytes_alloc, tablespace_name from
> sys.dba_data_files
> where TABLESPACE_NAME in
> ( 'AS_DATA_L','AS_DATA_ES','AS_INDEX_S','AS_INDEX_ES','AS_INDEX_ESL')
> group by tablespace_name ) b
> where a.tablespace_name (+) = b.tablespace_name
> order by pct_used,b.tablespace_name
> /
>
> Will post the Block Mapping soon
>
> BN

Answered the email you sent to me and don't want to repeat myself here but you'll never see it in DBA_EXTENTS. Look at using the capabilities of the DBMS_SPACE package to verify shrink candidates and find free space.

This package is documented in Morgan's Library at www.psoug.org.

-- 
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

Received on Tue May 01 2007 - 16:05:00 CDT