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

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 Thu Jun 21 2001 - 16:20:06 CDT