ORA-32773 and autoextend on/off

On oracle XE (but may be also on other 10g editions i presume), if you execute:

ALTER TABLESPACE demo AUTOEXTEND ON;

or

ALTER TABLESPACE demo AUTOEXTEND OFF;

You can get:

ORA-32773: operation not supported for smallfile tablespace DEMO

However, you can still turn autoextend on or off by issuing a more traditional:

ALTER DATABASE DATAFILE 'G:\demo01.dbf' AUTOEXTEND OFF;

If you create tablespace demo with the BIGFILE option:

CREATE BIGFILE TABLESPACE demo
DATAFILE 'G:\demo01.dbf' SIZE 100M AUTOEXTEND ON;

then you can turn autoextend on or off at will using the alter tablespace command above.

This behavior is consistent with the traditional architecture of Oracle, where a tablespace may have more than one datafile attached and each datafile could have a different autoextend setting. With the "new" bigfile tablespace type instead there is a single datafile, so there is no confusion as to which datafile should be made autoextensible or not.

Indeed, if you want to know if a tablespace can be autoextended, then you'll have to look at the underlying datafiles:

select tablespace_name, file_name, autoextensible
from dba_data_files
where tablespace_name = 'DEMO';

For a comparison between BIGFILE and SMALLFILE (traditional) tablespaces, see the Oracle XE SQL Reference Guide or the Oracle 10GR1 SQL Reference Guide.

See message translations for ORA-32773 and search additional resources.