Re: Space really used on TBS

On Apr 29, 6:32 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 29, 12: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
>
> Do you have OEM?  If on version 9, it has a real informative
> tablespace map graphic.  10R2 EM has something too, my brain isn't
> remembering it right now, you must be on 10 if you mention shrink?
> What exact (like 10.2.0.4) version are you on?  Have you done any mass
> deletes?  Have you tried a coalesce?  Do you purge deleted tables?
> What is your temporary tablespace?
>
> jg
> --
> @home.com is bogus.http://arstechnica.com/news.ars/post/20080415-gone-in-60-seconds-spam...

I solved,
it was a bug related to change data capture (just solved). Thanks to all for your attention
Regards
Mauro Received on Tue Apr 29 2008 - 13:48:51 CDT