Re: Space really used on TBS
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