Skip to Main Content

Oracle Database Discussions


For appeals, questions and feedback about Oracle Forums, please email Please ask technical questions in the appropriate category. Thank you!

autoextend of datafiles question

Justin BleisteinApr 28 2010 — edited Apr 28 2010
I just read up on autoextending datafiles in a tablespace for Oracle. I decided to research this because I was tired of the calls every now and then, which appear to be coming more and more frequently, to add datafiles to tablespaces, resize them, etc. What I can gather on how autoextending works, is you per datafiles with the alter database datafile ... command, set the datafile to autoextend on, then set two parameters related to it which are next and maxsize. Next tells Oracle how much to automatically increase the datafile by when it reaches the end of the datafile while allocating extents in that datafile. I get that. I'm just still unclear about the maxsize parameter. What does that mean? Let me attempt to understand it via a hypothetical scenario:

I have a tablespace called app, which holds a datafile: /u01/oradata/app01.dbf. This file is initially 1 gig in size.
Now, let's say the file fills with extents by the app, and fills up. At that point the app receives an error from Oracle saying that it cannot extend any longer in the datafile for the app tablespace. Now usually I just login, and either add a datafile like /u01/oradata/app02.dbf to the tablespace to add 1 gig, or resize the already existing app01 datafile, then tell the app admin to kick the job off again, and then it works fine, until app02 or whatever fills again.

So, I learn about the autoextension of datafiles Oracle feature, and I go ahead and issue the alter database datafile '/u01/oradata/app01.dbf' autoextend on next 100m maxsize 5g;. From what I understand of how it works, when an extent fills the app01 datafile instead of just throwing an error to the app again, Oracle will extend this datafile by the next value - 100m. Then once the app fills up that datafile by another 100m, Oracle will automatically extend the datafile by another 100m, and keep extending every 100m until it does this 50 times. Then it will be 5 gig worth of space which Oracle autoextended - 100M X 10 = 1000M(1 gig) X 5 = 5000M (5 gig). At that point what happens? The datafile reached the 5 gig maxsize limit of autoextension. I know the alert log of the database will get an error, but what is the DBAs' expected reaction this? Can I just reset the datafile maxsize, and then it will continue to autoextend some more? What is the point of maxsize.

I am thinking about setting the maxsize autoextend argument to unlimited, so the command would be: alter database datafile '/u01/oradata/app01.dbf' next 100m maxsize unlimited; This way all I have to do is keep track of the filesystem space. Are there any downsides to setting maxsize to unlimited? Should I do this?

Thanks in advance.

--Justin Richard Bleistein

Edited by: user476575 on Apr 28, 2010 6:47 AM
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 26 2010
Added on Apr 28 2010