Re: Empty BLocks (Holes) in the Datafile

On May 1, 5:05 pm, DA Morgan <damor..._at_psoug.org> wrote:
> bnsa..._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 atwww.psoug.org.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

bnsa, your terminology is not very clear. You delete rows from a table which in turn removes index entries that point to those rows. However the tables and indexes deleted from still occupy their space allocations. Daniel covered how to find this space.

Your drop database objects such as tables and indexes which removes the objects space allocations resulting in free space showing up in dba_free_space and less extents showing up in dba_extents. If you dropped tables and indexes and you do not see that fact reflected in dba_extents and dba_free_space then that is a definite issue that should be captured with before and after queries on the dictionary views that you can post on a Service Request with Oracle support.

HTH -- Mark D Powell -- Received on Tue May 01 2007 - 18:35:23 CDT