ORA-32773: operation not supported for smallfile tablespace USERS
Tablespace autoextend cannot be modified for all the datafiles. So we have to specify the particular datafile to enable or disable autoextend
SQL> select tablespace_name,autoextensible from dba_data_files where tablespace_name='USERS';
TABLESPACE_NAME AUT
------------------------------ ---
USERS YES
Command for tablespace will not work if it is smallfile tablespace as it can contain multiple datafiles
SQL> alter tablespace USERS autoextend off;
alter tablespace USERS autoextend off
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace USERS
Below is the error code with recommendation
SQL> !oerr ora 32773
32773, 00000, "operation not supported for smallfile tablespace %s"
// *Cause: An attempt was made to perform an operation which is supported
// only for bigfile tablespaces, e.g. resize tablespace.
// *Action: Use the appropriate clause of the ALTER DATABASE DATAFILE
// command instead.
Check the status of tablespace if it is bigfile
SQL> select tablespace_name,bigfile from dba_tablespaces where tablespace_name='USERS';
TABLESPACE_NAME BIG
------------------------------ ---
USERS NO
Check the physical datafile location
SQL> select file_name,TABLESPACE_NAME,autoextensible from dba_data_files where tablespace_name='USERS';
FILE_NAME TABLESPACE_NAME AUT
-------------------- ------------------------------ ---
/apps01/oradata/orcl USERS YES
19xusers01.dbf
Modify the autoextend status to ‘NO’ for specific physical datafile
SQL> alter database datafile '/apps01/oradata/orcl19xusers01.dbf' autoextend off;
Database altered.