what is the difference

Posted on 2014-04-06
Last Modified: 2014-04-08
What is the difference if we create a datafile in a tablespace instead of AUTOEXTEND on.
Question by:tonydba
  • 3
  • 3
  • 3
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39981629
If you create a data file with auto extend on then you don't have to explicitly add up chunks to the data file when ever it gets full..

oracle will do it automatically for you...

for ex: there is some table space called users where in there is some data file /usr/space/user01.dbf

if this file user01.dbf has been set to auto extend on. for ex: if the file is currently 400Mb and the tablespace is full , then the file automatically gets extended to next extent allocated say 200mb.. now the file will automatically become 600mb for you,, without any one doing any activity, however you have to specify the maximum size at the time of creation..

there are difference in opinions whether to use it or not., but this is one of a good feature which oracle provides

you can figure out more from various blogs
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39981825
>>instead of AUTOEXTEND on

There is no 'instead of'.  When you add a datafile it is either autoextensible or it isn't.

Author Comment

ID: 39981896
so let me clarify,

that means tablespace full datafiles occupied  ?

data will go to next datafile only when first datafile is filled ..?
LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 39982025
>>data will go to next datafile only when first datafile is filled ..?

When an object needs space it allocates an extent from a data file.  It finds the extent by looking at the free space list in the data files associated with the allocated data files.

This is done by a series of internal algorithms and internally maintained lists.  If is not done from 'beginning' to 'end' of the individual file.

For example: You have a tablespace with one datafile that is 50% full.  You add a new datafile.  Oracle needs a new extent for a table.  That extent may be in the brand new data file even though the original one is only half full.  You really cannot control this.

Based on this question and a lot of your previous ones, I would suggest you spend some time in the Concepts guide.  It does a great job talking about Oracle's architecture and how things are done.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39982336
--->that means tablespace full datafiles occupied  ?

If tablespace is full, then if the data file is with option auto extend on, then it will auto adjust the next extent,

-->data will go to next datafile only when first datafile is filled ..?

how and where the data goes is entirely managed by oracle when the data file is given an option of auto extend, based on the algorithm what steve has suggested above.

Inshort, if there is a data file with auto extend on, and the data file has reached to a particular file say 400mb, upon becoming full, it will automatically get readujusted to 600 mb(say next extent is 200mb).
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39983941
>>then if the data file is with option auto extend on, then it will auto adjust the next extent,

Not exactly correct.  It will extend the data file by the value specified in INCREMENT_BY.  Once the data file has been extended, then the extent is allocated.

It also isn't extended when 'full'.  A file extension is triggered when Oracle cannot locate enough contiguous free space for the size of the extent it is trying to allocate.

For example:
Say you are trying to allocate a 1 Gig extent.  DBA_FREE_SPACE can show 100 gig of free space but none of it is 1 Gig of contiguous space.  Therefore, it cannot grab the extent and must extend the data file.
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 39983963
yes steve.. i meant the adjustment by the parameter value increment by and also in case of auto extend, the file will grow only upto the max size parameter, if there is max size specified of 1G, then even if the free space is available, it cannot grow to the next extent even if contiguous space is available..!!

Author Comment

ID: 39986479
I've requested that this question be closed as follows:

Accepted answer: 0 points for tonydba's comment #a39981896

for the following reason:

Thank you...

Author Closing Comment

ID: 39986480
Thank you,

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB.Net - CSV to Oracle table 4 52
case statement in where clause 5 42
Performance issue with case statement in oracle 11G 7 49
SQL Developer 6 36
Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now