On Apr 29, 6:32=A0pm, joel garry <joel-ga...@[EMAIL PROTECTED]
> wrote:
> On Apr 29, 12:11=A0am, Mauro Pagano <mauro.pag...@[EMAIL PROTECTED]
> wrote:
>
>
>
> > 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.*,
> > > > =A0 =A0 =A0 =A0TRUNC (a.segment_size / tbs_size * 100, 2)
segment_pe=
rc_used,
> > > > =A0 =A0 =A0 =A0b.tbs_freespace,
> > > > =A0 =A0 =A0 =A0TRUNC (b.tbs_freespace / c.tbs_size * 100, 2)
freespa=
ce_perc,
> > > > =A0 =A0 =A0 =A0c.tbs_size
> > > > =A0 FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
> > > > =A0 =A0 =A0 =A0 =A0 FROM dba_extents
> > > > =A0 =A0 =A0 =A0 =A0WHERE tablespace_name =3D 'USER_CDC_DATA') a,
> > > > =A0 =A0 =A0 =A0(SELECT =A0 tablespace_name, SUM (BYTES) / 1024 /
102=
4
> > > > tbs_freespace
> > > > =A0 =A0 =A0 =A0 =A0 =A0 FROM dba_free_space
> > > > =A0 =A0 =A0 =A0 =A0 =A0WHERE tablespace_name =3D 'USER_CDC_DATA'
> > > > =A0 =A0 =A0 =A0 GROUP BY tablespace_name) b,
> > > > =A0 =A0 =A0 =A0(SELECT =A0 tablespace_name, SUM (BYTES) / 1024 /
102=
4 tbs_size
> > > > =A0 =A0 =A0 =A0 =A0 =A0 FROM dba_data_files
> > > > =A0 =A0 =A0 =A0 =A0 =A0WHERE tablespace_name =3D 'USER_CDC_DATA'
> > > > =A0 =A0 =A0 =A0 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:
>
> > > =A0SELECT b.tablespace_name, a.*,
> > > =A0 =A0 =A0 =A0 TRUNC (a.segment_size / tbs_size * 100, 2)
segment_per=
c_used,
> > > =A0 =A0 =A0 =A0 b.tbs_freespace,
> > > =A0 =A0 =A0 =A0 TRUNC (b.tbs_freespace / c.tbs_size * 100, 2)
freespac=
e_perc,
> > > =A0 =A0 =A0 =A0 c.tbs_size
> > > =A0 =A0FROM (SELECT SUM (BYTES) / 1024 / 1024 segment_size
> > > =A0 =A0 =A0 =A0 =A0 =A0FROM dba_extents
> > > =A0 =A0 =A0 =A0 =A0 WHERE tablespace_name =3D 'USER_CDC_DATA') a,
> > > =A0 =A0 =A0 =A0 (SELECT =A0 tablespace_name, SUM (BYTES) / 1024 /
1024=
> > > =A0tbs_freespace
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM dba_free_space
> > > =A0 =A0 =A0 =A0 =A0 =A0 WHERE tablespace_name =3D 'USER_CDC_DATA'
> > > =A0 =A0 =A0 =A0 =A0GROUP BY tablespace_name) b,
> > > =A0 =A0 =A0 =A0 (SELECT =A0 tablespace_name, SUM (BYTES) / 1024 /
1024=
tbs_size
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0FROM dba_data_files
> > > =A0 =A0 =A0 =A0 =A0 =A0 WHERE tablespace_name =3D 'USER_CDC_DATA'
> > > =A0 =A0 =A0 =A0 =A0 =A0 AND status =3D 'AVAILABLE'
> > > =A0 =A0 =A0 =A0 =A0GROUP 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? =A0If on version 9, it has a real informative
> tablespace map graphic. =A010R2 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? =A0Have you done any mass
> deletes? =A0Have you tried a coalesce? =A0Do you purge deleted tables?
> What is your tem****ary tablespace?
>
> jg
> --
> @[EMAIL PROTECTED]
is
bogus.http://arstechnica.com/news.ars/post/20080415-gone-in-6=
0-seconds-spam...
I solved,
it was a bug related to change data capture (just solved).
Thanks to all for your attention
Regards
Mauro
|
