Over Extended

Autoextend can be very helpful to busy DBAs by allowing Oracle to automatically increase file size when necessary. Some DBAs swear by it. Others swear at it, as it can be one of the largest nightmares a DBA can experience when left to its own devices by not providing a maximum size the file should attain.

Setting a file in a tablespace to autoextend is a fairly easy task as long as the user performing the actions has DBA or SYSDBA privileges:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2000M;

Database altered.

SQL>

And, not every datafile in a tablespace needs to be set to autoextend -- you can select one or more datafiles and leave others unaltered. The only true physical limit for autoextend datafiles is the size of the file system where they reside. Which is where the 'swearing at autoextend' part of the discussion begins.

While it may be considered wise by some to set datafiles to autoextend, doing so without first restricting their size is a grievous error. Executing the statement shown below:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on;

Database altered.

SQL>

is, well, irresponsible and careless, as the only restriction you've provided Oracle for the file size is the edge of the disk (figuratively speaking). Oracle will continue to extend this datafile ad infinitum, that is until it reaches the physical end of the storage media, when the process, and likely the database, will come to a grinding halt, displaying the (possibly nauseating) error:

ORA-01237: cannot extend datafile /whackenhut/smackdoodle/endoplasm/flurst01.dbf

followed by the relevant operating system errors indicating you have no space left on the desired device. And, by now, you have corrupted data in this tablespace and your tablespace (or, worse, database, depending upon how widespread this corruption becomes) needs to be restored and recovered. Yes, it (hopefully) ensures your backup and recovery strategy is sound, but I'm fairly certain that management wasn't expecting to test that process by having datafiles attempt to extend beyond their physical limits.

On leveraged systems (systems where more than one database may reside) autoextend, in any form, isn't a wise idea as you now have multiple databases competing for limited resources (disk space) and whichever database gets to that available space first, wins. And you also have X times more opportunities to reach the end of the physical media, and you can do it so much faster when 13 databases are competing for disk space on the same devices.

Okay, so you have autoextend datafiles and can't change that fact (management says 'We need them'); how do you monitor the size? Hopefully a MAXSIZE was set and, if so, it's pretty easy. The DBA_DATA_FILES view provides almost all of the necessary information (what's missing is the db_block_size, and that's fairly easy to find):

SQL> select file_name, bytes, maxbytes,
  2         increment_by*(bytes/blocks) "INCREMENT",
  3         maxbytes-bytes remaining,
  4         (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
  5  from dba_data_files
  6  where autoextensible = 'YES'
  7  /

FILE_NAME                       BYTES   MAXBYTES  INCREMENT  REMAINING EXTENSIONS
-------------------------- ---------- ---------- ---------- ---------- ----------
/d909/data/users01.dbf       52428800  314572800   10485760  262144000         25

SQL>

Notice the current size, the maximum size, the incremental value, space remaining and the extensions available are reported, to give you an idea of how much room is left to allocate to your autoextend datafile. You could execute this query on a daily basis to see how quickly or slowly your datafile is growing, then plan for either modifying the maxsize or adding another datafile. And all of this presumes you have sufficient disk space for the remaining extensions to occur; monitoring autoextend tablespaces also means monitoring the disk space at the operating system level to ensure that you don't autoextend yourself out of a database.

Can you set a datafile, already configured to autoextend, to have a maximum size? Certainly, and it's done with the same command used to turn autoextend on with a maximum size. To change the setting for the file we set at the beginning of this post we would:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend on maxsize 2200M;

Database altered.

SQL>

and, voila!, it will now extend to 2200 M instead of the 2000 M we originally set as its limit.

Of course one can always turn off autoextend:

SQL> alter database datafile '/whackenhut/smackdoodle/endoplasm/flurst01.dbf' autoextend off;

Database altered.

SQL>

On the whole I cannot recommend to anyone the use of autoextend for datafiles. Others may disagree, and that's what makes this such an interesting world in which to live. It's your choice to make; the decision should be a wise one, not one of convenience. I'd prefer to be 'inconvenienced' by a monitoring tool or script rather than the necessity of restoring a tablespace, or a database, because autoextend was implemented and left unrestricted.

As I learned early on, don't over-extend yourself; it can only get you into trouble later.