how do you know when to add space to tablesapce?

From: Robert Yeh <robert.yeh_at_qwest.com>
Date: Thu, 21 Jun 2001 16:38:22 -0400
Message-ID: <9gtlmi$3kv$1@bob.news.rcn.net>

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 Received on Thu Jun 21 2001 - 15:38:22 CDT