Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, oracle.

Asked: September 08, 2017 - 4:50 am UTC

Last updated: September 13, 2017 - 7:31 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi Team,

I have checked the tablespace is autoextend on and i wanted to checked -
Q.1 When it will grow ?
Q.2 How much it will grow ?
Q.3 Can we do anytime auotextend ON or Off in live production ? Will it impact on DB ?
Q.4 If the size of autoextend is reached till 32 GB then what is next we keep that file as it is and add new file with autoextend on ?



Thanks

and Connor said...

In earlier versions, datafile increments were very simple - when you datafile could not hold a requested for extent, we would grow it by the autoextend amount, and if none was specified, we'd grow by enough to hold that new extent.

There are problems with this - it takes time to grow a file, and initialise the blocks ready for data. So in more recent releases, we have a more proactive approach. See this doc for the SMC0 process

https://docs.oracle.com/cloud/latest/db112/REFRN/bgprocesses.htm#REFRN104

One of its jobs is to keep an eye on growth rates for a datafile, and it will "ahead of time" grow a datafile if it expects that extent allocation will be needed there in the near future. Obviously if you just hammer a datafile with new segments, the old behaviour will resume, but SMC0 is why sometimes can see a datafile grow when you might be thinking it was not required.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle oracle, September 08, 2017 - 9:01 am UTC

Thanks for Reply.

I just wanted the questions which i mentioned Because as i checked all sites it shows the growth of datafile is not mentioned then

show parameter db_block_size if is 8192 the

SELECT INCREMET_BY*8120/1024/1024 "MB" FROM DBA_DATA_FILES WHERE FILE_NAME = '/..........DBF';

So i can decide the space on OS level if my tablespace is autoexted when it is required it will take this much space and it will growth on this volume. As well as if there is space issue on OS we can stop the autoextend and add manual when required It is ok in production to on off ?

Thanks

Connor McDonald
September 11, 2017 - 8:26 am UTC

DBA_DATA_FILES.AUTOEXTENSIBLE will tell you if a file is autoextend or not.

You can enable or disable autoextend whenever you like, eg

SQL> alter database datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF' autoextend off;

Database altered.

SQL> alter database datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF' autoextend on next 100m;

Database altered.




oracle oracle, September 11, 2017 - 11:35 am UTC

Thanks for reply,

I have mentioned the query to check the growth of the file when autoexend will on.

So, could you pls let me know how much the next size will take if we don't mention the increment by or nextsize .

Thanks
Connor McDonald
September 13, 2017 - 7:31 am UTC

See the first sentence of my initial answer :-)

You can set it yourself

paul, September 11, 2017 - 2:49 pm UTC

If are concerned about what the default is you can (re)set it yourself.

select file_name, bytes, autoextensible, increment_by from dba_data_files where tablespace_name like 'APEX_1941389856444596'
"FILE_NAME"                "BYTES" "AUTOEXT" "INC_BY" 
"/u01/[..]/APEX_[..]596.dbf" 7929856 "YES"     3072

alter database datafile '/u01/app/oracle/oradata/orcl12c/orcl/APEX_1941389856444596.dbf' autoextend on NEXT 10M;
"FILE_NAME"                "BYTES" "AUTOEXT" "INC_BY" 
"/u01/[..]/APEX_[..]596.dbf" 7929856 "YES"     1280


You should be able to figure out how the system is being used (lots of inserts vs. mainly reporting) and extrapolate from their how fast things are growing and how you want to limit the INCREMENT_BY




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database