concerns and preferences is all they are though, it's worth noting. Born of experience - like the time an apps media tablespace autoextended by 8gb and then the dev teams wanted all 8 clone databases updated and suddenly we needed 64gb of space on the smaller dev environment - but experience and needs vary for sure. Niall On Tue, Mar 11, 2008 at 5:31 PM, Powell, Mark D <mark.powell@xxxxxxx> wrote: > Niall's concerns and preferences look pretty good to me. > > > -- Mark D Powell -- > Phone (313) 592-5148 > > > ------------------------------ > *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto: > oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Niall Litchfield > *Sent:* Tuesday, March 11, 2008 11:35 AM > *To:* Mayen.Shah@xxxxxxxxxx > *Cc:* joe_dba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx; > oracle-l-bounce@xxxxxxxxxxxxx > *Subject:* Re: LMTs > > Hi Shah, > > I'm not convinced that I'm any more likely to run out of space if my alert > is configured for 95% space usage or space used being within 5% of a > calculated max value. I entirely agree that the more databases and the more > tablespaces that you have the more difficult monitoring becomes - and the > more attractive automating space management becomes, but I'm always going to > have limits on > > 1. filesystem size > 2. tape capacity > 3. backup window duration > 4. filesystem size on clone databases > 5. filesystem size on standbys > > I'm sure there are others, which is why my personal preference is for > fixed size datafiles and manual intervention. That said I *only* have 29 > Oracle databases to monitor - though that does include 8 apps ones! I'm sure > that others will have significantly more. > > > On Tue, Mar 11, 2008 at 12:32 PM, <Mayen.Shah@xxxxxxxxxx> wrote: > > > > > Niall, > > > > 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" <niall.litchfield@xxxxxxxxx>* > > Sent by: oracle-l-bounce@xxxxxxxxxxxxx > > > > Mar 11 2008 05:37 AM Please respond to > > niall.litchfield@xxxxxxxxx > > > > To > > joe_dba@xxxxxxxxxxx cc > > oracle-l@xxxxxxxxxxxxx Subject > > Re: LMTs > > > > > > > > On Mon, Mar 10, 2008 at 2:30 PM, Joe Smith > > <*joe_dba@xxxxxxxxxxx*<joe_dba@xxxxxxxxxxx>> > > 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 > > EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M > > 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* > > **http://www.orawin.info* <http://www.orawin.info/> > > > > > > -- > Niall Litchfield > Oracle DBA > http://www.orawin.info > > -- Niall Litchfield Oracle DBA http://www.orawin.info