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: <>
Date: Thu, 15 Mar 2007 14:19:14 -0400
Message-ID: <>

Yes, I understand and that is part of the art and science and I have gone both ways. Filling up a tablespace is monitored, and then again extending the datafiles for the tablespace means you have to have disk space available.

This of course is done by montitoring free disk space which in our case we have at least two monitoring tools on that job. EM Grid, and Foglight, and as well the system administrator, as even another level besides the DBA.

So as long as we are within a reasonable bounds of free space we are fine. Also we do not have large loading going on, and we are not growing datafiles very fast.

I can see putting a large limit on the TEMP or UNDO datafiles just in case.

In any event, Auto extending the files not my priority with the question. I am trying to get such feedback on the increment by portion of the question.

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: [] Sent: Thursday, March 15, 2007 1:30 PM
To: Patterson, Joel; Subject: RE: Auto Extend On, Increment By

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 

[] On Behalf Of
		Sent: Thursday, March 15, 2007 12:42 PM
		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 - 13:19:14 CDT

Original text of this message