Re: LMTs

From: <>
Date: Tue, 11 Mar 2008 08:32:18 -0400
Message-ID: <>


Only problem in setting autoextensible to off is if you run out space in middle of business day, some transactions will fail and rest will be the history......

In large environment with many production database, with many more tablespaces it becomes difficult to monitor and maintain manually to make sure each tablespace has enough free space. I am using all tablespace autoextensible with fixed upper limit. I also have set up monitoring (patrol in my case) to send e-mail any time any tablespace is autoextended and mobile alert when actual size is within 5% of maxsize. If any tablespace has multiple datafile, I have kept only one datafile as autoextensible.

Mayen Shah

"Niall Litchfield" <> Sent by:
Mar 11 2008 05:37 AM
Please respond to

Re: LMTs

On Mon, Mar 10, 2008 at 2:30 PM, Joe Smith <> wrote: CREATE TABLESPACE data
DATAFILE '/FS/data_s01.dbf' size 2000m autoextend on next 1m maxsize 12000m,

              '/FS/data_s02.dbf' size 2000m autoextend on next 1m maxsize 12000m
SEGMENT SPACE MANAGEMENT AUTO; How do you control the size of LMTs.  

If I remove the "autoextend on next 1m" I can't use the "maxsize" keyword.  

How do I restrict the size of the datafiles for LMTs Hey Joe (always wanted to say that sorry)  

You have a choice. Either you want the datafiles to grow as needed and limit the total size to which they can grow - i.e to be autoextensible - in which case it makes sense that you need both the amount by which to grow each time and the absolute limit. Alternatively you know how big you want them to be and you just specify the fixed size for the datafile (no autoextension at all).  

I happen to prefer the latter - not least because it then becomes easy to tell when you are running out of space in a tablespace (how much free space is left), whereas when the datafiles are autoextensible it's very easily to miscalculate how full a tablespace is. I also like to change control space operations because they have an impact on clones, backups dataguard space requirements and so on. If you do prefer to let Oracle handle the growth then I'd suggest a rather larger next size than 1m. Once you get to 2gb of data every time you add 1mb more data you'll be growing the datafile which is a lot of growth operations. You'll also likely cause more filesystem fragmentation - though you might not care about that.        

Niall Litchfield
Oracle DBA 

Received on Tue Mar 11 2008 - 07:32:18 CDT

Original text of this message