Re: (no subject)

From: Vikram Goel <vgoel_at_pts.mot.com>
Date: 1996/03/15
Message-ID: <4ic23b$h6e_at_lserv1.paging.mot.com>#1/1

Adrian,

This works on V7.x, I am pretty shure it will work in V6 also, try it out.

Create the below view:

REM **************************************************************
REM * NAME object_blocks.sql                                     *
REM * PURPOSE: To see each objects block placement in a datafile *
REM * AUTHOR: Vikram Goel                                        *
REM * DATE: Jan 16, 1995.                                        *
REM *************************************************************

create view lcl_object_extents as

  select u.file# file_no,
         u.block# low_block,
         u.block# + u.length - 1 high_block,
         o.name name,
         'TABLE' obj_type

  from tab$ t, uet$ u, obj$ o

  where t.clu# is null
    and t.file# = u.segfile#
    and t.block# = u.segblock#
    and t.obj# = o.obj#

union all
  select distinct

         u.file# file# ,
        u.block#,
        u.block# + u.length - 1 ,
        o.name name ,
        'CLUSTER'

  from tab$ t, uet$ u, obj$ o

  where t.clu# is not null
    and t.file# = u.segfile#
    and t.block# = u.segblock#
    and t.clu# = o.obj#

and t.clu# = o.obj#
union all

  select u.file#,
        u.block#,
        u.block# + u.length - 1,
        o.name name,
        'INDEX' kind

  from ind$ i, uet$ u, obj$ o

  where i.file# = u.segfile#
    and i.block# = u.segblock#
    and i.obj# = o.obj#

union all

  select u.file# ,
        u.block# ,
        u.block# + u.length - 1,
        un.name,
        'ROLLBACK SEGMENT'

  from undo$ un, uet$ u
  where un.file# = u.segfile#
    and un.block# = u.segblock#
union all

  select u.file#,
        u.block# ,
        u.block# + u.length - 1,
        'TEMP SEGMENT' ,
        'TEMP SEGMENT'

  from uet$ u, seg$ s

  where s.file# = u.segfile#
   and  s.block# = u.block#
   and  s.type = 3

union all
  select file# ,

        block#,
        length + block#,
        'FREE EXTENT',
        'FREE EXTENT'
  from fet$;
     group by o.name,  io.io_type;

REM To use this do :
REM SELECT * from lcl_mon_object_extents REM order by file_no,low_block,high_block. REM
REM the diff betwen the low and high block is the available blocks REM multiply this by your db block size to get bytes. REM To create this view execute as 'SYS' and grant select to a user.

Hope this helps

--
Vikram Goel                          Motorola email: vgoel_at_pts.mot.com
Sr. Oracle DBA - Consultant
Aerotek Inc.                         My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426 


In article <4i92q1$a94_at_romeo.logica.co.uk>, brooksa_at_logica.com writes:


>I am trying to construct a query that will return a list of the total 


>sizes of blocks of contiguous free space in a tablespace.

>

>I have tried to use the dba_free_space table but cannot see how I can 

>achieve 'contiguous' part.

>

>I am using Oracle 6.0.32 (!) and would like to use this information for

>assessing the optimal size for new a object's initial extent and I don't 

>want to defragment the tablespace!

>-- 

>Adrian Brooks

>

>Any views expressed are my own and not of my employer.

>

>

Received on Fri Mar 15 1996 - 00:00:00 CET