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

You May Also Like

CONTENT parameter in datapump
Find sessions consuming lot of CPU
Clone a database using dbca command in oracle 19c -New feature
Apply database patch from OEM Cloud control
How to drop a database in postgres
Steps for changing public hostname for a standalone grid infrastructure
How to change spfile in Oracle RAC.
How to install oracle client in silent mode using response file
how to change archivelog destination in oracle
_optimizer_ignore_hint

From This Website

how to send mail using utl_mail in oracle 11g
How to use oratop tool for oracle database monitoring
Oracle database Security Assessment Tool
How to get the execution plan for a SQL between two AWR snapshots
CONTENT parameter in datapump
ORA-32773: operation not supported for smallfile tablespace
How to keep or delete columns/Variable of a data frame in R
how to use DBMS_PRIVILEGE_CAPTURE to capture privs in oracle 12c
Find sessions consuming lot of CPU
How to Read CSV files in R studio