Re: Space really used on TBS

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 Received on Tue Apr 29 2008 - 02:11:43 CDT