Skip to Main Content
  • Questions
  • Advantage and/or Disadvanage of using Autoextend

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jenny.

Asked: January 02, 2003 - 1:58 pm UTC

Last updated: April 12, 2021 - 5:29 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

I have a few questions related to autoextends:

1. Can you tell me the advantage and/or disadvantage of having "autoextend on" for every datafile I create in the database?
2. If I have a tablespace with all datafiles created to have autoextend on, then I turned it off after all datafiles have been filled, would this have any impact on the datafile or tablespace?

Thanks!

and we said...

1) some people like it (me), some people hate it. Some people wish to micromanage every last bit/byte. Some people don't want to be paged when a tablespace fills up but the file system has room.

(i use maxsize to make them not able to grow bigger then I want)

2) nope.

Rating

  (20 ratings)

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

Comments

Jenny

A reader, January 02, 2003 - 4:17 pm UTC

Tom,

Could you explain how maxsize works? For example, if I have the following create tablespace statement:
CREATE TABLESPACE tbspname_sml
DATAFILE '/usr/local/oracle/data02/${ORACLE_SID}/tbspname_sml_01.dbf' SIZE 524352K REUSE AUTOEXTEND ON NEXT 65536K MAXSIZE 786496K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K NOLOGGING
ONLINE
/

So, my datafile would be 512M, if it runs out of space, it will extend to the maxsize of 768M, would that make the datafile a total size of 1280M (512+768)? Could you elaborate more on it?

Thanks!

Tom Kyte
January 02, 2003 - 4:35 pm UTC

76.8m max

don't forget "next ##MB" for autoextensible datafiles

Xuequn Xu, January 02, 2003 - 4:21 pm UTC

I think specifying a reasonably big space growth rate in "next ..." clause would be a good idea, in addition to maxsize, if you want to set autoextend on (such as "alter database datafile '/path/to/db01.dbf' autoextend on next 10MB maxsize 4000MB").

Tom Kyte
January 02, 2003 - 4:44 pm UTC

sure, I make it be the extent size of my LMT.

Answer to Jenny's question

David Gibbs, January 02, 2003 - 4:25 pm UTC

Jenny,

Max size is just that max size.

In your example your file is initially 512K (or was that M). It will grow by 64K (or M) - that is your next size until it reaches a max size of 768K (or M - you get my point).

Thanks!

Jenny, January 02, 2003 - 5:17 pm UTC

For your answers.

Mike, May 08, 2003 - 12:45 pm UTC

config1:
One huge file in the tablespace, 15G;

config2:

more than one file in the tablespace, total of 15G;

Comparison of the two, what's the advantage / disadvantage?

TIA

Monthly Database Maintenance

SHGoh, July 15, 2004 - 11:58 pm UTC

Dear Tom,

We are using Oracle database version 8.1.6 and plan to have a montly database maintenance. Do you recommend to reorganization all the tables by using alter table XYZ move tablespace SAMETABLESPACE STORAGE.... during the montly database maintenance?

By using Alter table xxx MOVE tablespace command, do we still need to recreate the constraints, rebuild indexes, and recompile the views. Can I find any scripts that you have written for this purpose in this website? Thanks.

Rgds
SHGoh

Tom Kyte
July 16, 2004 - 10:59 am UTC

I am categorically against automated rebuilds/re-orgs of anything.

don't do it. not neccesary. if you find an object in need of a rebuild, lets talk then.

otherwise what you do is incur downtime, you then tell the end users "it is because oracle needs reorg (false)", and they believe "oracle cannot be up all of the time" and it just cascades from there. you do not reorg like this. it is RARE to reorg and definitely nothing i would even consider thinking about doing every month.

Thanks Tom.

SHGoh, July 16, 2004 - 11:10 pm UTC

Dear Tom,

Thanks for the information.

Rgds
SHGoh

Autoextend over allocated

John Hook, November 15, 2004 - 3:27 pm UTC

Is there an easy way to determine when you've overallocated a disk?

Tom Kyte
November 15, 2004 - 9:08 pm UTC

yes, it returns an error immediately (you cannot "over allocate" a disk -- you can only try to over allocate and it'll fail)

Autoextend

Phil, May 26, 2005 - 5:54 am UTC

Tom
Thanks for the advice - silly question but how do I 'tell' if autoextend is on in the first place? I can use TOAD to see but was interested in the query TOAD uses to provide this. In my standard DB I have quite small indx and users tablespaces and so need to know if they will expand automatically.
Thanks
Phil

Tom Kyte
May 26, 2005 - 9:42 am UTC

ops$tkyte@ORA9IR2> desc dba_data_files;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 FILE_NAME                                    VARCHAR2(513)
 FILE_ID                                      NUMBER
 TABLESPACE_NAME                              VARCHAR2(30)
 BYTES                                        NUMBER
 BLOCKS                                       NUMBER
 STATUS                                       VARCHAR2(9)
 RELATIVE_FNO                                 NUMBER<b>
 AUTOEXTENSIBLE                               VARCHAR2(3)</b>
 MAXBYTES                                     NUMBER
 MAXBLOCKS                                    NUMBER
 INCREMENT_BY                                 NUMBER
 USER_BYTES                                   NUMBER
 USER_BLOCKS                                  NUMBER
 

what about auto allocated extents...

Craig, July 28, 2005 - 11:37 am UTC

Earlier in this thread, you mentioned you like to set autoextend for a datafile to the LMT extent size. You also advocate the use of autoallocated extents where the extent sizes change automatically depending on the size of the segment.

Is it a concern if the extent size grows and the autoextend isn't big enough to accommodate? Would it be advisable to set autoextend on next 8M if we use autoallocated extents?

Tom Kyte
July 28, 2005 - 12:15 pm UTC

It will do as many autoextends as it needs, 1m/8m multiple of 8m would be fine.

multiple autoextends...

Craig, July 28, 2005 - 2:16 pm UTC

Tablespace A uses 8M extents and its datafile autoextends by 1M intervals.
Tablespace B uses 8M extents and its datafile autoextends by 8M intervals.

When they need a new extent, will A be less efficient the B? Is it more costly to autoextend a datafile multiple times to accommodate a new extent or does it matter at all?

Tom Kyte
July 28, 2005 - 3:40 pm UTC

yes it would be marginally less efficient. But remember, the goal is "this will eventually grow very slowly (8m is not even the top limit here)". So, it will pretty much not be noticable except in an extreme case.

Heck, have it grow 64m at a time if you like

Are Autoextends logged anywhere?

Steve, January 17, 2006 - 10:39 am UTC

Are Autoextends logged anywhere? I don't think
so but I just want to make sure

Tom Kyte
January 17, 2006 - 4:01 pm UTC

no, they are not.

Great discussion

A reader, November 01, 2006 - 12:18 pm UTC

Tom, thanks for sharing your knowledge.

I have a question for your regarding autoextend: You say you let datafiles on autoextend on (say of 1M), but you set a maxsize for them, however, with today technology (cluster file systems, logical luns with stripped disk underneath, etc) one tends to put datafiles on the same filesystem, and then when they are many, you just set some "logical" number as their maxsize. However, problems can occur if the filesystem becomes full and you didn't measured well. I was wondering if, within Oracle, even using Java, one can access the underlying filesystem (being Windoze or Unix) to get the underlying metrics of disk storage (like how much space is free on a given filesystem), just like OEM on 10gR2 seems to do it (with the metrics alert, etc). That would be great in generating reports of tablespaces apparently being "full" but the underlying disk having enough space as of this not being an alert anymore.

Thanks for your time.

Thanks again!

Tom Kyte
November 01, 2006 - 6:14 pm UTC

you can use a java stored procedure for that, yes.

Looks like..

A reader, November 02, 2006 - 2:22 pm UTC

.. Java IO classes leaves much to be desired.

</code> http://forums.java.net/jive/thread.jspa?threadID=454&start=0&tstart=0 <code>

I believe I will have to implement a platform especific solution (for example, using df on *nix and some vbscript on windows). That's the way OEM does it I believe. Too bad I can't rather have a database especific solution.

Thanks for your time Tom, and if you know of anything else, let me know.

Thanks again!

Autoextend debate

Steve, December 20, 2006 - 7:40 pm UTC

A colleague has suggested that using qutoextend is not a good idea and will refuses to use it. Consequently, he gets regular requests to extend the tablespaces in his databases as they run out of space. I accept that some people, as you say at the top, may feel uncomfortable using autoextend and it's use can therefore come down to personal preference. However, he claims that there are good technical reasons why it should not be used. I asked him to explain and got this analogy in response. I would appreciate your comments!

"Oracle is a great steward, and will keep placing bottles of champagne in the fridge. The steward wants to keep the fridge looking good, so it rearranges the bottles, relizes there is more space, and adds more bottles. He keeps doing this, because, the fridge has plenty of space.

Soon he finds half full bottles of champagne, so he starts adding these to the fridge. He also notices that there are some only a quarter full of champagne, and some with no champagne at all, these get added to the refrigerator.

The steward keeps working until refrigerator gets full (ie: out of space).

Now, do we buy a new refrigerator (ie: add disk)?

Now Oracle did his job splendidly, the bottles are neatly in fridge, but now how do we tell the empty bottles from the full ? The only way is to empty the refrigerator, and look at each bottle and remove the empty ones and combine the partials bottles to make one full bottle (ie: database reorganization).

So, now the question is, what is the best way to proceed? Oracle will use all the space for data whether it needs it or not, without any human intervention, when autoextend is turned on. Oracle will consume allocated storage in the way it feels, this includes setting aside large amounts of storage for limited data. The only way this storage can be reclaimed, is through a database reorganization. Its a long and time consuming task that will require some outage time. "

I think he may be confusing either tablespace fragmentation (no longer an issue with LMT), or maybe lots of truncates and/or insert /*+append */ - or maybe not!

Tom Kyte
December 20, 2006 - 8:45 pm UTC

so the steward would use maxsize - wouldn't they?

I don't get it, unless you drop tons of tables (which no one would really do?) the bottles never get half full....

segments do not shrink unless and until DBA says "thou shall shrink".


not sure where they were going with this, but the wine steward thing didn't get my attention.

Re: Autoextend debate

Charlie B., December 21, 2006 - 4:41 pm UTC

Steve -

Perhaps your friend is not used to using LMT's? When an LMT gets a request for space it does not require the space to be contiguous, unlike DMT's. So in this unusual metaphor, when Oracle picks up a bottle to put in the fridge:

- an unopened bottle goes in the fridge
- an opened bottle gets poured into other opened bottles and then thrown away, storing the champagne but not the bottle.
- an empty bottle gets thrown away immediately.

Oracle doesn't ask for another refrigerator until all the bottles in the fridge (sealed _or_ previously opened) are full.

If this isn't it, I guess we'll need more details about his refrigerator.

Hope that helps...?

Creating Tablespace

Maverick, February 04, 2008 - 10:01 am UTC

I have some information about a tablespace in dba_tablespaces for , say tablespace1

1.Can I generate a "create tablespace" script from it [oracle 10g]?
I need to create same to another database for some tables that are in this tablespace.

2.Can I create a tablespace without mentioning Datafile in it?


Thanks for your help.
Tom Kyte
February 04, 2008 - 4:12 pm UTC

1)
ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLESPACE', 'USERS' ) from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
-------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/home/ora10gr2/oradata/ora10gr2/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE
  '/home/ora10gr2/oradata/ora10gr2/users01.dbf' RESIZE 462684160



2) yes, you set db_create_file_dest and then:

ops$tkyte%ORA10GR2> show parameter create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /home/ora10gr2/oradata/ora10gr
                                                 2
ops$tkyte%ORA10GR2> create tablespace test;

Tablespace created.

Too many Datafiles ?

Steve, December 18, 2008 - 7:13 am UTC

Hi Tom,

I have been searching for pros and cons to having a large
number of datafiles. We are currently at 3200 datafiles,
each with a MAXSIZE of 8GB (most have already attained the
8GB). In order to increase our tablespaces, we have 2 options:

o Add datafiles
o increase the maxsize of existing datafiles

We are concerned that 3200 files is getting too large and
are leaning towards the second option.

Do you have an opinion or a link that can list the pros
and cons? I'm guessing things like the checkpoint process
will be affected.

Thanks TOm

Autoextend

Ashish, June 28, 2012 - 11:47 pm UTC

Hello,

I have one tablespace with 200 datafiles, in which one datafile is in autoextend on mode. My tablespace size is 600gb which is actual size as per dba_data_files, i daily monitor free_space in tablespace using dba_free_space, i am surprised i already have 40 gb free space in tablespace which is decreasing daily by 200 mb but also my actual tablespace size which is 600gb is increasing after 2 or 3 days around 500mb. As i think once actual size will get filled then only whole tablespace size should increase, but here after 2 or 3 days whole tablespace size is increasing as well as daily free space is decreasing.

Please guide, i am new in this.

Thanks,
Ashish


Tom Kyte
June 29, 2012 - 10:33 am UTC



So, you have 200 datafiles
And you have 600gb of files allocated
There is 40gb of free space
You seem to add 200mb of data every day.

What is happening is that we allocate space in a round robin fashion across the files - file1 allocates an extent, then file2, then file3 and so on. So, we are hitting your autoallocate file and it is full and so we extend it when it is its turn to give some space.


Tablespace autoextend goes wildly allocating space to max size

Amin Adatia, March 31, 2021 - 1:10 pm UTC

I am on Exadata and Database 18.5

I have tablespaces created as bigfile initial 20G next 256G maxsize 20T

Since 2019 we have had 5 occurrences of the tablespace extending to the 20T limit. The average usage is around 400G

Any idea on how to find out what the circumstances are when the tablespace extension goes wild?

Regards
Connor McDonald
April 12, 2021 - 5:29 am UTC

If I had to guess - you had a parallel direct mode operation (eg CTAS, insert-append, index build etc) with a lot of slaves. Each slave wants to load into its *own extent*, which is only trimmed at the end of the operation.

eg 20 slaves = 20 extents = 20 x 256G = lots of space used very quickly.

256G as a next extent seems .... excessive.