Solved

what is the difference

Posted on 2014-04-06
9
341 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
  • 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 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.
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
 
LVL 76

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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
Value of 0's not appearing. 9 50
Oracle SQL Select Statement 19 58
Oracle Pivot 2 34
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 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

18 Experts available now in Live!

Get 1:1 Help Now