Autoextend data files have been available in Oracle since Oracle 8. Auto-extend allows a DBA to set a file size to an initial allocation, but allows Oracle to grow the file automatically as objects increase in size.
Autoextend works well with the thin provisioned storage from ScaleIO, and ensures that capacity is not wasted.
However, the operation to grow a data file is relatively intensive, and DBAs should take care to ensure that production data files are not continuously extending. The increment by which a data file grows can be set using the NEXT clause as follows:
SQL> alter tablespace USERS add datafile '+DATA' size 1024M autoextend on next 8M;
DBAs should also specify a MAXSIZE to limit the overall growth of the data file. This is especially important where users have some flexibility over the queries or transactions they may execute against the production database:
SQL> alter tablespace USERS add datafile '+DATA' size 1024M autoextend on next 8M maxsize 2048M;
Without the MAXSIZE clause, a rogue update or insert statement might generate gigabytes of invalid data before an error is generated.
The MAXSIZE clause is especially important for the TEMP tablespace, where OLAP users may launch complex queries that generate excessive amounts of temporary data.
Autoextend should NOT be used for the UNDO tablespaces of a production database. Setting the UNDO data files to auto-extend mode presents the opportunity for a transaction to fail if the UNDO tablespace is unable to extend due to storage exhaustion.
In this scenario, the UNDO data for the database is now corrupt.
During recovery, Oracle will apply redo data to data files before opening the database to users. It then applies pending UNDO. If that UNDO data is corrupt, the recovered database will crash.
Typically in this scenario, the only option is to restore from the last known good backup.