Question

How to check if Oracle tablespaces are in autoextend mode and what is the defined maximum size (in Oracle 11/11r2 the maximum is 32Gb)?

Answer

The following SQL from the aleph user prompt will show whether each Oracle datafile is autoextensible. (Autoextensibility is defined at the datafile level.):

> s+ aleph_admin
select TABLESPACE_NAME, FILE_NAME,AUTOEXTENSIBLE,MAXBYTES from dba_Data_files where TABLESPACE_NAME like 'TS__';

Check the values in AUTOEXTENSIBLE column; if it’s NO this feature is not enabled on the file in column FILE_NAME.

• Article last edited: 08-Oct-2013