Re: Space really used on TBS

From: <fitzjarrell_at_cox.net>
Date: Mon, 28 Apr 2008 10:56:11 -0700 (PDT)
Message-ID: <c33054a3-b01f-4d17-8f0d-6f8db6ba512d@a1g2000hsb.googlegroups.com>


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 Received on Mon Apr 28 2008 - 12:56:11 CDT

Original text of this message