Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> how do you know when to add space to tablesapce?
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
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