RE: autoextend

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 25 Sep 2006 22:14:38 -0400
Message-ID: <004201c6e111$85d15520$0c00a8c0@Thing1>

I have not tested recent behavior, but there have been releases where it was possible to get autoextend to make the OS file bigger than Oracle could address. Then you get a catch-22 that there is free space listed in the dictionary in that file, so even if you add a new file, Oracle will continue trying to use the free space that it cannot address. If I recall correctly the error was not detected until a write into an unaddressable block was actually attempted. That is, extents from free space containing blocks that could not actually be written to were allocated to tables.

I'm not sure whether this is possible any more with dictionary managed tablespaces or at all with locally managed tablespaces.

There may be other reasons, depending on your facilities, for limiting the maximum size of a single file. For example, if you have removable disk packs for backup you may elect to keep files small enough to fit on a single pack. That is just one example. At any rate, you can still add a file to the tablespace if the current last file approaches any predetermined maximum size according to what fits your facilities. I do recommend having at most one file in autoextend mode per tablespace at a time, usually the last file added.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com Sent: Monday, September 25, 2006 11:13 AM To: Dennis Williams; oracle-l
Subject: Re: autoextend

Dennis,

Regarding your point #2 (MAXSIZE). We are debating setting it to unlimited. The argument for not setting it to unlimited is that we currently have the MAXSIZE set to about 3 times the current size of the file. And we only need to expand this file once in the last several years. And I think there is a general sence of not giving Oracle too much access.

I am however leaning towards setting it to unlimited precisely to avoid that "gotcha". This is a very high profile application and we can't afford to have it running out of space. Plus we will have OEM monitoring set up to notify us when the tablespace is close to being filled and therefore we should know when it is going to be expanded.
with the monitoring turned on, I can't see a scenario when unlimited maxsize will cause problems. Is there anything that I 'm missing here?

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

                                                                           
             "Dennis Williams"                                             
             <oracledba.willia                                             
             ms_at_gmail.com>                                              To 
                                       genegurevich_at_discoverfinancial.com  
             09/21/2006 11:04                                           cc 
             AM                                                            
                                                                   Subject 
                                       Re: autoextend

Gene,

Nope, it will only extend when it needs. Look at RESIZE to immediately change the size.

Also be sure to mention your platform and Oracle version.

But more importantly, consider two issues: 1. How will the monitoring be done at the server level? For example if that is the responsibility of your system administrators, then you need to talk to them first.
2. Be cautious with the MAXSIZE limit. This can become a hidden "gotcha". You think since autoextend is turned on your users won't run out of space. Then months from now you get a call in the middle of the night and after digging around you discover the MAXSIZE was hit.

Dennis Williams

On 9/21/06, genegurevich_at_discoverfinancial.com < genegurevich_at_discoverfinancial.com> wrote: Hi everybody:

  I am looking into turning autoextend on on a few of my prod tablespaces   and
  I want to make sure that
  i understand it all correctly. I am issuing the command

  alter database datafile FILENAME autoextend on increment XXXM maxsize   XXXM;   This will force Oracle to increase the size of the FILENAME by XXXM   (assuming that
  this space is available in the filesystem) whenever the tablespace runs   out
  of space.
  It will not however affect the way the space is allocated for the object   within my
  tablespace. Am I correct here?

thank you

Gene Gurevich

--
http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Sep 25 2006 - 21:14:38 CDT