Re: autoextend system

From: <Kip.Bryant_at_Vishay.com>
Date: Mon, 21 Aug 2000 14:40:43 -0700
Message-Id: <10596.115161@fatcity.com>

Michael,

I get the impression that autoextend was kind of buggy pre V8. I finally started using it this year and so far so good. And I would definitely set max size, too. Anyway, here's a script for monitoring datafiles on autoextend that someone else posted. I added one minor revision to it to drop out fully extended autoextend datafiles.

column cur heading "Data|File|Size"
column max heading "Max|Auto|Extend"
column inc heading "Auto|Extend|Increm"
column ext heading "No Of|Free|Extnts"

column hifre heading "High|Free|Extent"
column totfre heading "Tot|Free|Space"
select substr(d.tablespace_name,1,11) tablespace,

        substr(d.file_name,20,17) filename,
        to_char((d.blocks * 8192)       /1048576,'9,999')||'M'  cur,
        to_char((f.maxextend * 8192)    /1048576,'9,999')||'M'  max,
        to_char((f.inc * 8192)          /1048576,'9,999')||'M'  inc,
        to_char(count(*),'9,999')                               ext,
        to_char(max(s.bytes)            /1048576,'9,999')||'M'  hifre,
        to_char(sum(s.bytes)            /1048576,'9,999')||'M'  totfre
from sys.dba_data_files d,sys.filext$ f,sys.dba_free_space s
where (d.file_id         = f.file#
  and s.file_id(+)      = f.file#) and

not (d.blocks = f.maxextend)
group by d.tablespace_name,d.file_name,d.blocks,f.maxextend,f.inc /

Kip

|Advantages:
|Less admin to do

|Disadvantages:
|See above

|Currently we use the autoextend feature as that is how I inherited the
|databases when our DBA left. All our tables only grow (no deletes) so it is
|a good thing for me until I learn how to do this job better. I do not
|understand a couple of the responses regarding having files all over the
|place though. Autoextend will never add any files to the OS. Autoextend is
|datafile-specific so it just allows THAT datafile to grow to the MAXSIZE that
|was specified or UNLIMITED, which is the default. Once you reach that
|MAXSIZE, you still need to manually add a new datafile for more data.

|In summary, if you use it, be sure to set MAXSIZE or your files can grow
|forever and can eventually corrupt your db (see Note 112011.1). Per that
|Note, you should set MAXSIZE accordingly. Other than that, the only thing to
|worry about is making sure you have all the diskspace needed if all your
|files were at their MAXSIZE. You still need to check your datafiles though
|so you know when to add another, but it can be done easily at the OS level
|now. For example, all my files except SYSTEM have MAXSIZE of 4GB so I can
|very quickly see when one is near that (it will increase in increments of
|the NEXT parameter).

|There isn't a good need for it if your tables don't grow or grow slowly.

|Michael Ray
|DBA in training