Re: Space really used on TBS

From: <fitzjarrell_at_cox.net>
Date: Tue, 29 Apr 2008 06:05:13 -0700 (PDT)
Message-ID: <4869da70-62ef-4caf-a338-2ef3e289ce59@x41g2000hsb.googlegroups.com>

On Apr 29, 2:11 am, Mauro Pagano <mauro.pag..._at_gmail.com> wrote:
> On Apr 28, 7:56 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Apr 28, 10:42 am, Mauro Pagano <mauro.pag..._at_gmail.com> wrote:
>
> > > Hi,
> > > executing the following query
>
> > > SELECT b.tablespace_name, a.*,
> > >        TRUNC (a.segment_size / tbs_size * 100, 2) segment_perc_used,
> > >        b.tbs_freespace,
> > >        TRUNC (b.tbs_freespace / c.tbs_size * 100, 2) freespace_perc,
> > >        c.tbs_size
> > >   FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
> > >           FROM dba_extents
> > >          WHERE tablespace_name = 'USER_CDC_DATA') a,
> > >        (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024
> > > tbs_freespace
> > >             FROM dba_free_space
> > >            WHERE tablespace_name = 'USER_CDC_DATA'
> > >         GROUP BY tablespace_name) b,
> > >        (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 tbs_size
> > >             FROM dba_data_files
> > >            WHERE tablespace_name = 'USER_CDC_DATA'
> > >         GROUP BY tablespace_name) c
>
> > > I get this result
>
> > > USER_CDC_DATA,112,0.72,5076.8125,32.75,15500
>
> > > where 112Mb are used (0.72% of tbs total available space)
> > > 5076Mb are free (32.75% of tbs total available space)
> > > 15500Mb is the tbs size
>
> > > Why I have less then 1% used and only 32.75% free?
> > > Where I'm wasting space?
> > > Please note that shrink objects on tbs doesn't provide any benefit.
>
> > > Regards
> > > Mauro
>
> > Possibly one or more of your datafiles for that tablespace is offline;
> > try this modification to your query and see what is returned:
>
> >  SELECT b.tablespace_name, a.*,
> >         TRUNC (a.segment_size / tbs_size * 100, 2) segment_perc_used,
> >         b.tbs_freespace,
> >         TRUNC (b.tbs_freespace / c.tbs_size * 100, 2) freespace_perc,
> >         c.tbs_size
> >    FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
> >            FROM dba_extents
> >           WHERE tablespace_name = 'USER_CDC_DATA') a,
> >         (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024
> >  tbs_freespace
> >              FROM dba_free_space
> >             WHERE tablespace_name = 'USER_CDC_DATA'
> >          GROUP BY tablespace_name) b,
> >         (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 tbs_size
> >              FROM dba_data_files
> >             WHERE tablespace_name = 'USER_CDC_DATA'
> >             AND status = 'AVAILABLE'
> >          GROUP BY tablespace_name) c
>
> > David Fitzjarrell
>
> David,
> thanks a lot for your reply.
> Unfortunately both datafiles are online so your query returns the same
> data of mine.
> Have you any idea about?
> Regards
> Mauro- Hide quoted text -
>
> - Show quoted text -

You might try running this query to see how that tablespace is 'mapped':

Select

	'free space' owner,
	'    ' object,
	file_id,
	block_id,
	blocks
from
	sys.dba_free_space
where
	tablespace_name = upper('&&1')

union
select

	substr(owner,1,20),
	substr(segment_name,1,32),
	file_id,
	block_id,
	blocks
from
	sys.dba_extents
where
	tablespace_name = upper('&&1')

order by 3,4;

Then, create this procedure as SYS and run it for every table in the suspect tablespace:

create or replace
procedure show_space

( p_segname in varchar2,
  p_owner   in varchar2 default user,
  p_type    in varchar2 default 'TABLE' )
as
    l_free_blks                 number;

    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_LAST_USED_BLOCK           number;

    procedure p( p_label in varchar2, p_num in number )     is
    begin

        dbms_output.put_line( rpad(p_label,40,'.') ||
                              p_num );

    end;
begin

    dbms_space.free_blocks

    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
      freelist_group_id => 0,
      free_blks         => l_free_blks );

    dbms_space.unused_space
    ( segment_owner     => p_owner,
      segment_name      => p_segname,
      segment_type      => p_type,
      total_blocks      => l_total_blocks,
      total_bytes       => l_total_bytes,
      unused_blocks     => l_unused_blocks,
      unused_bytes      => l_unused_bytes,
      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
      LAST_USED_BLOCK => l_LAST_USED_BLOCK );

    p( 'Free Blocks', l_free_blks );
    p( 'Total Blocks', l_total_blocks );
    p( 'Total Bytes', l_total_bytes );
    p( 'Unused Blocks', l_unused_blocks );
    p( 'Unused Bytes', l_unused_bytes );
    p( 'Last Used Ext FileId', l_LastUsedExtFileId );

    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );     p( 'Last Used Block', l_LAST_USED_BLOCK ); end;
/

Spool the output from the following query to a file:

select 'exec show_space('''||object_name||''','''||owner||''','''|| object_type||''')'
from dba_objects
where tablespace_name = 'USER_CDC_DATA'
and object_type in ('TABLE','INDEX','CLUSTER','PARTITION');

then run the resulting script, spooling that output to a file. I don't know what you'll find, but you might find what is consuming your space.

David Fitzjarrell Received on Tue Apr 29 2008 - 08:05:13 CDT