Re: Space really used on TBS
- From: joel garry <joel-garry@xxxxxxxx>
- Date: Tue, 29 Apr 2008 09:32:24 -0700 (PDT)
On Apr 29, 12:11 am, Mauro Pagano <mauro.pag...@xxxxxxxxx> wrote:
On Apr 28, 7:56 pm, "fitzjarr...@xxxxxxx" <orat...@xxxxxxx> wrote:
On Apr 28, 10:42 am, Mauro Pagano <mauro.pag...@xxxxxxxxx> 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
.
- Follow-Ups:
- Re: Space really used on TBS
- From: Mauro Pagano
- Re: Space really used on TBS
- References:
- Space really used on TBS
- From: Mauro Pagano
- Re: Space really used on TBS
- From: fitzjarrell@xxxxxxx
- Re: Space really used on TBS
- From: Mauro Pagano
- Space really used on TBS
- Prev by Date: Re: library cache miss ratio very high - how to find out the rot cause
- Next by Date: Re: Shorten Query
- Previous by thread: Re: Space really used on TBS
- Next by thread: Re: Space really used on TBS
- Index(es):
Relevant Pages
|
