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
ORA-tablespace TROUBLESHOOTING