what is the difference

What is the difference if we create a datafile in a tablespace instead of AUTOEXTEND on.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>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.

Wasim Akram ShaikCommented:
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


slightwv (䄆 Netminder) Commented:
>>instead of AUTOEXTEND on

There is no 'instead of'.  When you add a datafile it is either autoextensible or it isn't.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

tonydbaAuthor Commented:
so let me clarify,

that means tablespace full datafiles occupied  ?

data will go to next datafile only when first datafile is filled ..?
Wasim Akram ShaikCommented:
--->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).
slightwv (䄆 Netminder) Commented:
>>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.
Wasim Akram ShaikCommented:
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..!!
tonydbaAuthor Commented:
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...
tonydbaAuthor Commented:
Thank you,
All Courses

From novice to tech pro — start learning today.