Solved

what is the difference

Posted on 2014-04-06
9
376 Views
Last Modified: 2014-04-08
What is the difference if we create a datafile in a tablespace instead of AUTOEXTEND on.
0
Comment
Question by:tonydba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
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

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7110041173291

http://www.dba-oracle.com/t_alter_autoextend_on.htm
0
 
LVL 77

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.
0
 

Author Comment

by:tonydba
ID: 39981896
so let me clarify,

that means tablespace full datafiles occupied  ?

data will go to next datafile only when first datafile is filled ..?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 77

Accepted Solution

by:
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.

http://docs.oracle.com/cd/E11882_01/server.112/e40540/physical.htm#CNCPT1082
0
 
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).
0
 
LVL 77

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.
0
 
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..!!
0
 

Author Comment

by:tonydba
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...
0
 

Author Closing Comment

by:tonydba
ID: 39986480
Thank you,
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

635 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