Skip to Main Content
  • Questions

  • LMTS autoextend values for NEXT and MAXSIZE

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Brian.

Asked: April 10, 2008 - 10:50 am UTC

Last updated: April 11, 2008 - 8:10 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I have a tablespace on Oracle 9.2.0 that I wish to convert from DMTS to LMTS for obvious performace reasons. However I'm just confused how autoextend uses the NEXT and MAXSIZE parameters.

I need to create a 8Gb tablespace but can only have each datafile a maximum of 4Gb each. I can add more datafiles if needed later and extend the tablespace.

I will them move existing tables from the DMTS to the LMTS.

I aim to use the following command:

create tablespace <tb name> extent management local autoallocate segment space management auto datafile
'C:\...file1.data1' size 4000M autoextend on next 40M maxsize 4000M,
'C:\...file2.data1' size 4000M autoextend on next 40M maxsize 4000M

Am I right is assuming that when the data starts to get moved into the new LMTS then it will grow in 40M chunks in the first datafile upto a maximum datafile size of 4Gb, then start using the second datafile?

If the maxsize was higher than 4000M am I right in assuming the datafile will increase above 4Gb up to the Maxsize value?

Many thanks

and Tom said...

we tend to allocate extents in a round robin fashion - you cannot expect that it'll fill up a file and then move on in general - it will just use any file it feels like to autoextend - it may or may not fill up a file to 4000m and then move on - it might pick the last file to fill up first or the first one.

Just know that

a) your files will grow in 40m increments
b) they will stop when they are 4000m in size


The maxsize limits the size the file will autoextend to - yes, so if you set it above 4000m, it will be able to grow larger than 4000m

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Is it right?

A reader, April 11, 2008 - 5:26 am UTC

Oracle does not care about how many datafiles belong to a tablespace.
So Even you have 100 datafiles to a tablespace,oracle would consider it as a single unit constituting a tablespace and hence would add the space anywhere it finds.
It is for this reason you cannot delete a datafile once added.
Tom Kyte
April 11, 2008 - 8:10 am UTC

ah, but you can drop a datafile these days....

if the file is empty
and if the file is not the 'first' file of the tablespace

ops$tkyte%ORA10GR2> create tablespace demo
  2  datafile '/tmp/demo1.dbf' size 1m, '/tmp/demo2.dbf' size 1m, '/tmp/demo3.dbf' size 1m;

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t (x int) tablespace demo;

Table created.

ops$tkyte%ORA10GR2> alter tablespace demo drop datafile '/tmp/demo1.dbf';
alter tablespace demo drop datafile '/tmp/demo1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace DEMO


ops$tkyte%ORA10GR2> alter tablespace demo drop datafile '/tmp/demo2.dbf';

Tablespace altered.

ops$tkyte%ORA10GR2> alter tablespace demo drop datafile '/tmp/demo3.dbf';
alter tablespace demo drop datafile '/tmp/demo3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


and yes, in general, if you have 100 files, we'll allocate space as we see fit (we tend to round robin, to stripe the data over datafiles, but not always)

Nice clarification

amr, April 11, 2008 - 1:55 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.