Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

what is the difference

Posted on 2014-04-06
9
Medium Priority
?
382 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

722 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