Re: Space really used on TBS

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-spambot-cracks-livehotmail-captcha.html