Re: LMTs

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: joe_dba@xxxxxxxxxxx
  • Date: Tue, 11 Mar 2008 09:37:58 +0000

On Mon, Mar 10, 2008 at 2:30 PM, Joe Smith <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
>
  • References:
    • LMTs
      • From: Joe Smith

Other related posts: