Re: Empty BLocks (Holes) in the Datafile

From: <bnsarma_at_gmail.com>
Date: 1 May 2007 09:15:02 -0700
Message-ID: <1178036102.102098.194670@o5g2000hsb.googlegroups.com>

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 Received on Tue May 01 2007 - 11:15:02 CDT