PROBLEM:

While enabling autoextend for an tablespace, got error like ORA-32773: operation not supported for small file table space.
SQL> alter tablespace SMALLTS autoextend on;
alter tablespace SMALLTS autoextend on
*
ERROR at line 1:
ORA-32773: operation not supported for smallfile tablespace SMALLTS

 

SOLUTION:

A standard tablespace(i.e smallfile tablespace) can multiple datafiles, So if you want to enable/disable auto extend , then it need to done for each datafile of the tablespace. It cannot done directly on the tablespace.
SQL> select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name=’SMALLTS’;

TABLESPACE_NAME                BIG
—————————— —
SMALLTS                         NO

 

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name=’SMALLTS’

TABLESPACE_NAME FILE_NAME AUT
———————– ——————————————————————- —
SMALLTS /dmdata02/oradata/BSDMSIT2/smallts01.dbf NO
SMALLTS /dmdata02/oradata/BSDMSIT2/smallts02.dbf NO

 

SQL> Alter database datafile ‘/dmdata02/oradata/BSDMSIT2/smallts01.dbf’ autoextend on;
Database altered;

SQL> Alter database datafile ‘/dmdata02/oradata/BSDMSIT2/smallts02.dbf’ autoextend on;
Database altered;

 

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name=’SMALLTS’

TABLESPACE_NAME FILE_NAME AUT
———————– ——————————————————————- —
SMALLTS /dmdata02/oradata/BSDMSIT2/smallts01.dbf YES
SMALLTS /dmdata02/oradata/BSDMSIT2/smallts02.dbf YES

 

However for BIGFILE TABLESPACE , we can do this at tablespace level also , Because it can contain only one datafile.
SQL> create bigfile tablespace BIGTS datafile ‘/dmdata02/oradata/BSDMSIT2/bigts01.dbf’ size 1G;

Tablespace created.

SQL> select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name=’BIGTS’;

TABLESPACE_NAME FILE_NAME AUT
———————– ——————————————————————- —
BIGTS /dmdata02/oradata/BSDMSIT2/bigts01.dbf NO

SQL> select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name=’BIGTS’;

TABLESPACE_NAME         BIG
———————– —
BIGTS                   YES

 

SQL> alter tablespace BIGTS autoextend on;

Tablespace altered.

 

SQL> select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name=’BIGTS’;

TABLESPACE_NAME           FILE_NAME                                                         AUT
———————– ——————————————————————- —
BIGTS                     /dmdata02/oradata/BSDMSIT2/bigts01.dbf                            YES

 

 

SEE ALSO : TABLESPACE MANAGEMENT TUTORIAL IN ORACLE DB