Re: how do you know when to add space to tablesapce?

From: Ethan Post <Blah_at_Blah.com>
Date: Fri, 22 Jun 2001 05:49:20 GMT
Message-ID: <A%AY6.60972$%a.3059418@news1.rdc1.sdca.home.com>

Here is the script I like to use. We have a version of the script that runs on cron and greps out any tablespaces over 70% full. It also shows you how full the tablespace in relation to the data files ability to autoextend. However, I don't like to use autoextend.

• Ethan Post

select
tablespace_name,

round(sum(total_mb)-sum(free_mb)) cur_use_mb,
round(sum(total_mb)) cur_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(total_mb)*100) cur_percent_full,
round(sum(max_mb) - (sum(total_mb)-sum(free_mb))) free_space_mb,
round(sum(max_mb)) max_sz_mb,
round((sum(total_mb)-sum(free_mb))/sum(max_mb)*100) overall_percent_full

from
(
select
tablespace_name,
sum(bytes)/1024/1024 free_mb,
0 total_mb,
0 max_mb
from
dba_free_space
group by
tablespace_name
union
select
tablespace_name,
0 current_mb,
sum(bytes)/1024/1024 total_mb,
sum(decode(maxbytes, 0, bytes, maxbytes))/1024/1024 max_mb from
dba_data_files
group by
tablespace_name)
group by
tablespace_name;

"Daniel A. Morgan" <Daniel.Morgan_at_attws.com> wrote in message news:3B326506.9BDCE981_at_attws.com...
> Robert Yeh wrote:
>
> > The goal here is to determine when to add space to a tablespace. I have
 a
> > home made script to give me a quick view of tablespace usage. I find
 the
> > total data files space and how much space has been allocated by database
> > objects. Here is my script:
> >
> > select a.TABLESPACE_NAME, a.datafile_size, b.allocated_size,
> > (b.allocated_size)/a.datafile_size*100 PCT_USED,
> > (a.datafile_size-b.allocated_size) FREE_SPACE,
> > b.next_extent/1024/1024 "MaxNext",
> > a.initial_extent/1024/1024 "DefaultInit",
> > a.next_extent/1024/1024 "DefaultNext"
> > from (select a.tablespace_name, sum(b.bytes)/1024/1024 datafile_size,
> > a.initial_extent, a.next_extent
> > from dba_tablespaces a, dba_data_files b
> > where a.TABLESPACE_NAME = b.TABLESPACE_NAME
> > group by a.tablespace_name,
> > a.initial_extent, a.next_extent) A,
> > (select a.tablespace_name, sum(c.bytes)/1024/1024 allocated_size,
> > max(c.next_extent) next_extent
> > from dba_tablespaces a, dba_segments c
> > where a.TABLESPACE_NAME = c.TABLESPACE_NAME
> > group by a.tablespace_name) B
> > where a.tablespace_name = b.tablespace_name(+)
> > order by 1
> >
> > I normally make sure the max. next extend does not go over the free
 space.
> > But this is not really an accurate way to tell since the allocated space
> > might not have any data in it.
> >
> > What script do you use to alarm you when to add space to tablespaces?
> >
> > Thanks
> > Bob
>
> I use an arbitrary percentage: 75% usage.
>
> Daniel A. Morgan
>
Received on Fri Jun 22 2001 - 00:49:20 CDT