Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Spaced Out with Autoextend By Dave Moore
Feb 2004, Oracle Professional

Dave Moore is author of Oracle Utilities by Rampant TechPress

Spaced Out with Autoextend
Dave Moore

Copyright © Pinnacle Publishing, a division of Lawrence Ragan Communications Inc., 2004.  Originally published in the February 2004 issue of Oracle Professional.  All rights reserved. 


Version 7 of the Oracle database supported the option for tablespaces to automatically grow past the size they were given initially.  Oracle could then utilize free space available on devices and, as a result,  applications would not fail because Oracle could allocate another extent to a segment.  The autoextend feature (specified for datafiles and listed in the DBA_DATA_FILES view) tells Oracle to automatically grow  to a certain limit or even UNLIMITED in which case Oracle can use all available space on the device.  This article explains the ramifications of using autoextend and focuses on the challenges it creates.  Specifically, this article demonstrates when Oracle is accurate with its space estimates and when it is not – missing on both sides of the correct number.  After reading this article, DBAs will know not to depend solely on the V$ or DBA_FREE_SPACE views in order to determine available free space.  In fact, SQL commands alone will not provide accurate results when dealing with tablespaces containing datafiles that can autoextend.    


Quick Review

Before we explore the anomolies in detail, let’s review some basics.   The table below contains five datafiles for one tablespace.  Take a moment to determine how many MB this tablespace can grow. 

Tablespace Test :  All sizes are in MB


File Name




OS Space Available



























Let’s walk through the exercise:

df_01:  This datafile has a size of 100 MB.  The autoextend option is on with a maxsize of 1000 MB.  This file could get as big as 1000 MB; however only 800 MB exists in the OS.  Therefore, it can only grow an additional 800 MB. 

df_05: This datafile has a size of 50 MB. The autoextend option is on with a maxsize of 1000.  Normally, this file could extend as much as 800 MB (OS space available) but this file is on the same device (device1) as df_01.  The space on device1 was previously counted for df_01 and it would be an error to count it again.  Although these two files could share the 800 MB between them, they can only use 800 MB total.  Oracle has no problem with creating multiple datafiles for the same tablespace on the same device with autoextend unlimited.  Therefore, df_05 can grow 0 MB.

df_02: This datafile has a size of  200 MB with autoextend off. Therefore maxsize and OS space are not applicable to this file.  Answer = 0 MB.

df_03: This datafile has a size of 300 MB.  The autoextend option is on with a maxsize of UNLIMITED.  Since the file can grow as large as the file system will support, in this case it can grow as much as 2000 MB.

df_04: This datafile has a size of 400 MB.  The autoextend option is on with a maxsize of 1000 MB.  However, only 900 MB exist in the OS on this file system.  df_04 can grow to 1000 MB for a difference of 600 MB (maxsize – allocated).  But wait … is there enough OS space available to support that?  Yes, we can use as much as 900 MB from the OS so we are fine.  Answer = 600 MB. 


800 + 0 + 0 + 2000 + 600 = 3400


This brings the amount of space that this tablespace can grow to 3400 MB.  This exercise proves that a datafile’s ability to autoextend depends on the amount of available disk space on the device.

When calculating the available free space, things are fairly straightforward until autoextend is utilized on a datafile.  And, as a result of utilizing this feature, many views we rely on so heavily become much less useful – mainly DBA_FREE_SPACE.  

Inadequate Measurements

DBAs typically begin with a query like the one below.   This appears to be useful information, but any reliance on this data alone will result in problems.


SQL> select tablespace_name, sum(bytes) "Free Space"

     from dba_free_space

     group by tablespace_name

     order by sum(bytes) asc;


TABLESPACE_NAME                Free Space

------------------------------ ----------

TS_TEMP                           5240832

TS_DEFAULT                        9459712

SYSTEM                           14102528

TS_RBS                           17315840

DEMO_SQL_1                       20969472

QA_TABLESPACE_ONLINE             62322688

TEMP1                           104855552


My first issue with the DBA_FREE_SPACE view is that it doesn’t show tablespaces with 0 bytes of free space.  A slight change to the query above could join to DBA_TABLESPACES for the ones that are missing from the view. I suppose since there is technically no free space, full tablespaces do not deserve to be listed in the free space view.  (Is no response a response? Is a balance of 0 still a balance?) If the tablespace has 1 byte free, it will be listed. Otherwise it is omitted.  This makes it slightly more challenging to find those tablespaces that are indeed already full.

Scenario 1: More Space Available Than Indicated by Oracle

In certain situations, Oracle tells us we are running low on space when in fact we have plenty. In the example below, a tablespace is created with one datafile of 5 MB that can automatically extend to use as much space as the file system can provide (UNLIMITED).


SQL> create tablespace test_tsp datafile     


     size 5M

     autoextend on

     maxsize unlimited;


Tablespace created.


SQL> select sum(bytes) from dba_free_space

     where tablespace_name = 'TEST_TSP';





According to the query result above, Oracle claims the tablespace has 5MB of free space available.  This is particularly annoying when my monitoring solution notifies me at 3:00 a.m. that the tablespace is low on space only for me to discover that this is not really a problem.  I quickly determine that my datafile will continue to expand on the file system and the system has plenty of available space.  False alarm.

The following command shows how much space is available on the file system, in this case my Windows XP machine:


C:\oracle9i\datafiles\MOORACLE>dir *.

 Volume in drive C has no label.

 Volume Serial Number is 304E-C1B1


 Directory of C:\oracle9i\datafiles\MOORACLE


11/29/2003  07:38 PM    <DIR>          .

11/29/2003  07:38 PM    <DIR>          ..

               0 File(s)               0 bytes

               2 Dir(s)  61,694,697,472 bytes free


This proves that I have more than 61 GB available before my space expires ... but DBA_FREE_SPACE told me I had only 5MB!  Yes, unfortunately, the DBA_FREE_SPACE view does not compensate for datafiles that can autoextend.

Checking the available disk space on Unix is slightly more involved.  First, you must know which device a datafile resides on. The df command is used in conjunction with the file name.  The ‘man’ page for df states “The df command displays the amount of disk space occupied by a mounted or unmounted file systems, the amount of used and available space, and how much of the file system’s total capacity has been used.”



$ df /usr/oracle/databases/asg920wa/datafiles/tools2.dbf | awk '{print $1}'

Next, the device name is used to determine the available disk information using the –k option of the df command:


$ df -k /data1 | awk '(NR!=1){(OFS=",");if(NF==6)print $4;else
if(NF==5)print $3}'


The output from the command shows the amount of space on this device is 2204644 K.  (Note that the df command is platform specific and the unit of measure (K, M, bytes) may vary depending on your operating system).

The initial query is modified below to only include permanent tablespaces and also indicate which tablespaces have datafiles that can autoextend.  This is a signal to the DBA that more research is required for an accurate space assessment.

SQL> col auto HEADING 'Autoextend?' FORMAT a11;

SQL> select a.tablespace_name, sum(a.bytes) "Free Space",

            max(b.autoextensible) "auto"

      from dba_free_space a, dba_data_files b, dba_tablespaces c

      where c.contents      = 'PERMANENT'

      and c.tablespace_name = a.tablespace_name

      and a.tablespace_name = b.tablespace_name

      group by a.tablespace_name

      order by sum(a.bytes) asc;


TABLESPACE_NAME                Free Space Autoextend?

------------------------------ ---------- -----------

TS_TEMP                           5240832 NO

TS_DEFAULT                        9459712 NO

SYSTEM                           14102528 NO

DEMO_SQL_1                       20969472 NO

QA_TABLESPACE_ONLINE             62322688 NO

TS_RBS                           69263360 YES

TEMP1                           104855552 NO

Scenario 2: Less Space Available Than Indicated by Oracle

In other situations, Oracle may indicate that we have enough space when in fact we do not.  The first scenario in which this occurs is when the value of maxsize is used as the actual maximum size.  The maxsize value is easily obtained with SQL and scripts may use this value to determine how much the file can grow.   However, there is no check within the Oracle kernel to verify that the amount of space specified by maxsize is actually available.  In other words, a datafile could reside on a device which has 100 MB of available disk space.  Given a specified maxsize of 500 MB, that number would be incorrect since the OS only has 100 MB available.  Any scripts that use maxsize as the true upper limit are potentially problematic. 

The second scenario in which a tablespace may falsely appear to have enough space is when the NEXT size for a segment within the tablespace is larger than the amount of free space available.  In this situation, a datafile may have enough space for another 20 MB, yet one segment in this file has a NEXT extent size of 40 MB.  This tablespace is as good as full as far as this segment is concerned.  Any monitoring scripts that use a low threshold, say 10 MB in this case, would never have triggered an alert before this error occurred.  This problem is resolved by executing the query below.  Any negative number in the DELTA column would indicate that no segment in this tablespace would be able to allocate their next extent.



SQL> select a.tablespace_name, a.largest_extent,  


       a.largest_extent-b.smallest_next_extent delta

       from (select tablespace_name, max(bytes) largest_extent

               from dba_free_space

               group by tablespace_name) a,

      (select tablespace_name, min (next_extent) smallest_next_extent

               from dba_segments

               where segment_type not in ('CACHE', 'TEMPORARY')

               group by tablespace_name ) b

        where a.tablespace_name(+) = b.tablespace_name;



------------------------------ -------------- -------------------- ----------

QA_TABLESPACE_ONLINE                 54517760                 2048   54515712

SYSTEM                               13641728                10240   13631488

TS_DEFAULT                            9459712                10240    9449472

DAVE_TEST                               10240                20480     -10240

TS_RBS                                5230592                10240    5220352



Slight modifications to the query could be made to show the largest next extent of a segment in the tablespace.  This would be an indication that certain segments contained in the tablespace will not be able to allocate their next extent – also a good thing to know. 

But once again this falls prey to autoextend.  The tablespace may not have enough space right now, but it will autoextend as it needs to create another extent.    The example above shows that tablespace DAVE_TEST will be unable to allocate an extent for the smallest segment in the tablespace.  However, this tablespace (and datafile) can autoextend and therefore it will be fine if there is enough free space in the operating system.


This article barely touched the surface of space management issues and autoextend.  Space Management is a complicated issue that deserves a much lengthier discussion.  Many scenarios were not discussed including the differences when using Locally Managed Tablespaces (LMTs).

Autoextend is a powerful feature and one that should be utilized in my opinion.  For most databases, tablespaces filling up can be avoided by leveraging this capability and allowing the datafiles to expand automatically.  For DBAs sensitive to applications that could quickly expire all OS space available unless a hard limit is enforced, the maxsize can be used in conjunction with autoextend as a safety net. 

When evaluating Oracle monitoring software, confirm that the vendor monitors data file usage at the OS level and does not rely solely on the DBA_FREE_SPACE view.  SQL commands alone will not provide accurate space reports when autoextend is used. In fact, a combination of operating system and SQL commands together are required to obtain accurate space usage information.  

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.






 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks