Oracle Tablespace Autoextend: Complete Guide

To achieve automatic expansion of Oracle database tablespace, you can follow the steps below:

1. Create a tablespace with autoextend property: When creating a tablespace, use the keyword “AUTOEXTEND” and specify “ON” to enable automatic extension. Example:

CREATE TABLESPACE example
DATAFILE '/path/to/datafile.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

1. Specify the increment and maximum size for automatic extension: When creating a tablespace using the “AUTOEXTEND” keyword, you can set the increment size for each automatic extension by specifying the “NEXT” keyword, and set the maximum size for the tablespace by specifying the “MAXSIZE” keyword. An example is shown as the above example.
2. To view the automatic extension properties of a tablespace, you can use the following SQL statement.

SELECT tablespace_name, autoextensible, increment_by, maxbytes
FROM dba_data_files
WHERE tablespace_name = 'example';

By following these steps, you will be able to enable the automatic extension feature for Oracle database tablespaces. As the amount of data in the tablespace increases, the database will automatically expand the size of the tablespace to accommodate more data.