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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s