Autoextend Tablespace in Oracle

I will explain Autoextend Tablespace in Oracle in this post.

If you want to learn more details about Tablespace or how to create tablespace in Oracle, read the following post.

https://ittutorial.org/create-tablespace-and-create-permanent-undo-and-temp-tablespace-add-datafile-tablespace-in-oracle/

Autoextend Tablespace in Oracle

Oracle DBA should use the Autoextend On feauture for tablespaces to set a tablespace to automatically extend itself by a specified amount when it reaches its maximum size limit.  If tablespaces’ autoextend is not enabled, then you are alerted when the tablespace reaches its critical or warning threshold size.

How to check autoextend on tablespace in oracle

You can check the autoextensible of datafiles using the following script.

select file_name,tablespace_name,maxbytes from dba_data_files where autoextensible='NO';

If the autoextensible of datafile is NO, autoextensible feature is not enabled for the related datafile, and datafile will not auto extend.

You can check the autoextensible datafiles using the following script.

select file_name,tablespace_name,maxbytes from dba_data_files where autoextensible='YES';

You can enable the autoextend for the existing datafiles as follows.

alter database datafile '+DATAC1/MSDB/DATAFILE/myDataTBS.476.928368179' autoextend on maxsize unlimited;

select 'alter database datafile '|| file_name|| ' '|| ' autoextend on maxsize unlimited;'
from dba_data_files where autoextensible='NO';

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'/oracle/oradata/TEST/NEW_TBS_TEST01.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED,
'/oracle/oradata/TEST/NEW_TBS_TEST02.dbf' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE NEW_TBS_TEST
DATAFILE
'+DATA' SIZE 4G AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Add Autoextend datafile to Tablespace

You can add new datafile to the tablespace , If Oracle ASM is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '+DATA' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

You can add new datafile to the tablespace , If File System is used, you can use the following script.

ALTER TABLESPACE NEW_TBS_TEST ADD DATAFILE '/oracle/oradata/TEST/NEW_TBS_TEST03.dbf' SIZE 15M AUTOEXTEND ON MAXSIZE UNLIMITED;

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )