On Apr 28, 7:56 pm, "fitzjarr...@[EMAIL PROTECTED]
" <orat...@[EMAIL PROTECTED]
> wrote:
> On Apr 28, 10:42 am, Mauro Pagano <mauro.pag...@[EMAIL PROTECTED]
> 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
|
