Re: Empty BLocks (Holes) in the Datafile

On May 1, 11:26 am, DA Morgan <damor...@xxxxxxxxx> wrote:
> bnsa...@xxxxxxxxx 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...@xxxxxxxxxxxxxxxx
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org


Greetings Daniel

Here is the info,


Candidate Datafile:

 Tablespace
Name                                                         Size(M)
Used(M) Used%
-------------------------
------------------------------------------------------------ -------
------- -----
AS_INDEX_ES               /u56/oradata/vrasdb/
AS_INDEX_ES_01.dbf                          6983    1250   18.00


HWM     TOTAL_BLOCKS    total_Mb        SHRINKAGE_POSSIBLE      
shrinkage_possible_mb

893739  896000  7000    2262    17.671875

14:56:34 sys@VRASDB> select min(BLOCK_ID), max(BLOCK_ID) from
dba_extents where FILE_ID=18;

MIN(BLOCK_ID) MAX(BLOCK_ID)
------------- -------------
            2        893755

Last Segment of the Datafile:

  1  select owner, segment_name, segment_type
  2  from
  3          (select owner, segment_name, segment_type, file_id,
relative_fno, block_id, max(block_id) over
  4                  (partition by file_id, relative_fno) max_block_id
  5           from dba_extents where file_id=18) a
  6* where block_id = max_block_id
13:39:14 sys@VRASDB> /


OWNER      SEGMENT_NAME                 Type
---------- ---------------------------- ----------
ASDBA1     XPKSPECIALDELPHICODEDETAILS  INDEX


Not sure if I move/rebuild this index into other TBS, how much space I
can get, so I wanted to get a map of the Blocks  for this datafile  to
see the Block distribution  and if  other BIG holes exist in the
datafile some where else in the datailfe

Came up with the following Query, Doesn't make sense, (I have not
added header Block)

For the same Segent the blocks look contigious, dont see the holes,

If the blocks are contigious, why am I seeing multiple blocks for each
segment?

The output is  not complete, there

  1  select SEGMENT_NAME,SEGMENT_TYPE,max(BLOCK_ID),BLOCKS,(BLOCKS
+max(BLOCK_ID)) "Actual_Next",
  2  lead(max(BLOCK_ID)) over(order by BLOCK_ID) Real_Next
  3  from dba_extents where FILE_ID=18
  4* group by
SEGMENT_NAME,block_id,SEGMENT_TYPE,TABLESPACE_NAME,BYTES,BLOCKS
14:47:16 sys@VRASDB> /

SEGMENT_NAME                   Type       MAX(BLOCK_ID)     BLOCKS
Actual_Next  REAL_NEXT
------------------------------ ---------- ------------- ----------
----------- ----------
XPKROUTINGTYPECODE             INDEX                  2
130         132        132
XPKBATCHRESTART                INDEX                132
130         262        262
XPKAPPLICATIONIDENTIFIER       INDEX                262
130         392        392
XPKCENTERROUTINGCRITERIA       INDEX                392
130         522        522
XFK1CENTERSTATE                INDEX                522
130         652        652


....

5335 rows selected.


Regards
BN

--
http://www.freelists.org/webpage/oracle-l


Other related posts: