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


Related Topics

ORA-32773: operation not supported for smallfile tablespace
ORA-01536: space quota exceeded for tablespace
TNS-01106: Listener using listener name has already been started
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
ORA-16855: transport lag has exceeded specified threshold
TNS-12542: TNS:address already in use
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
ORA-32771: cannot add file to bigfile tablespace
ORA-32774: more than one file was specified for bigfile tablespace
ORA-30034: Undo tablespace cannot be specified as temporary tablespace

You May Also Like

Steps for changing public hostname for a standalone grid infrastructure
ESTIMATE REQUIRED DISK SPACE FOR EXPORT USING estimate_only
COMPRESSION in datapump oracle
How to move spfile from file system to ASM in RAC
Schema replication using oracle goldengate
Hive installation with mysql database
Unified audit trail in Oracle 12c
ORA-01536: space quota exceeded for tablespace
COLS & COLSEXCEPT FILTER in goldengate
Lock account automatically with INACTIVE_ACCOUNT_TIME

From This Website

How to setup dataguard broker configuration (DG broker) in 12c
Multinode Hadoop installation steps
How to change the case (Lower to Upper and Vice Versa) in R
ERROR: No checkpoint table specified for ADD REPLICAT
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
Find the active transactions in oracle database
Steps for upgrading weblogic 12.1.2 to 12.1.3
Useful flashback related commands
How to install postgres database on mac os/linux
OGG-01028 partial record at sequence extract abended