Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Auto Extend On, Increment By

Re: Auto Extend On, Increment By

From: Tim Gorman <>
Date: Thu, 15 Mar 2007 17:12:41 -0600
Message-ID: <>

I agree with Jim, in general. Using AUTOEXTEND on a regular basis is a "hands-off" database administration style not very dissimilar to the kind of "hands-off" parenting style I seen all too many families (my own included, unfortunately), where the parent essentially tells their teenagers "I'll stock the kitchen with food, do your laundry, and keep a roof over your head, but you raise yourself." Setting boundaries and monitoring things is as necessary with databases as with kids (especially teenagers!), for the long-term health of both. Although permitting teenagers independence is one thing, permitting database applications that kind of "go-grow-yourself independence" is another thing entirely.

Having said that all that, I find great usage for AUTOEXTEND in migration situations, where a database is being loaded with god-knows-how-much data. For a temporary period, AUTOEXTEND can be utilized to permit load jobs from failing for stupid reasons. Once the migration is completed, then AUTOEXTEND should be disabled and normal boundaries and monitoring re-established. I'm sure that there is an analogy to raising teenagers somewhere in this, as well... :-)

Just my $0.02... wrote:
> Personally, I don't like to use AUTOEXTEND on any datafiles. On more
> than one occasion we've run into problems in which a datafile
> autoextended to the point where there was no more room in the
> filesystem. As a result, the issue now included not only the database,
> but the OS as well. And, while recovering from this situation (e.g.,
> moving a datafile to another filesystem) is not rocket science, it's yet
> another complication in our already complicated lives.
> I'd rather make use of monitors that warn us when we're close to filling
> a tablespace, at which time we have the luxury of deciding how to deal
> with the situation.
> Just my $0.02...
> =====================================
> Jim Silverman
> Senior Systems Database Administrator
> Solucient, LLC - A Thomson Company
> Telephone: 734-669-7641
> FAX: 734-930-7611
> E-Mail:
> From:
> [] On Behalf Of
> Sent: Thursday, March 15, 2007 12:42 PM
> To:
> Subject: Auto Extend On, Increment By
> I have proposed to have all datafiles on all databases
> autoextend with and increment size of 1280 blocks, (10Mbs).
> Responses I am getting back are like: The increment
> should be based on the size of the datafile and how rapidly it's
> increasing in size. How to measure growth.
> My reaction is like: Being consistent does not mean
> behavior will change in each situation. For datafiles that do not
> increase... they won't autoextend anyway. For datafiles that do
> extend, the ones that grow less will not extend very often. One
> datafile may extend once per week, and another twice a year. You don't
> have to have a 'size' that is proportional to how fast it grows.
> I'm wondering what the communities reaction is before I
> respond.
> Joel Patterson
> Database Administrator
> --

Received on Thu Mar 15 2007 - 18:12:41 CDT

Original text of this message