ORA-32773 Operation not Supported for SmallFile Tablespace

ORA-32773 : Operation not Supported for SmallFile Tablespace

ORA-ERROR 32773 : Operation not Supported for SmallFile Tablespace

Problem: While Enabling autoextend for a Tablespace,it will get an error like ORA-32773 :Operation not supported for small file table space

SQL>alter tablespace CHAITANYASMALLTBS autoextend on;

alter tablespace CHAITANYASMALLTBS autoextend on

*

ERROR ar line 1:

ORA_32773:operation not supported for small file tablespace  CHAITANYASMALLTBS

Solution:

A standard tablespace that is small file tablespace can have multiple datafiles,so if you want to enable or disable autoextend on so you have done for each datafile not for tablespace  it cannot be done directly on the tablespace

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME                     BIGFILE

------------------------------------------------------------

CHAITANYASMALLTBS                     NO

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

--------------------------------------------------------------------

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     NO

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     NO

SQL>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu02.dbf' autoextend on;

Database altered;

SQL>alter database datafile '/oradata/prod/chaitu03.dbf' autoextend on;

Database altered;

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_file where tablespace_name like 'CHAITANYASMALLTBS';

TABLESPACE_NAME        FILE_NAME               AUT

--------------------------------------------------------------------

CHAITANYASMALLTBS  /oradata/prod/chaitu01.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu02.dbf     YES

CHAITANYASMALLTBS  /oradata/prod/chaitu03.dbf     YES

In BIGFILE TABLESPACE we can do autoextensible in tablelevel also but it have only one datafile 

SQL> create bigfile tablespace CHAITANYABIGFTBS datafile '/oradata/prod/chaitubigfile01.dbf' size 2G;

Tablespace Created.

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

--------------------------------------------------------------------------------

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         NO

SQL>select tablespace_name,BIGFILE from dba_tablespaces where tablespace_name like ' CHAITANYABIGFTBS';

TABLESPACE_NAME               BIG

--------------------------------------------

CHAITANYABIGFTBS                YES

SQL>alter tablespace CHAITANYABIGFTBS autoextend on;

Tablespace altered

SQL>select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'CHAITANYABIGFTBS';

TABLESPACE_NAME        FILE_NAME                                     AUT

--------------------------------------------------------------------------------

CHAITANYABIGFTBS         /oradata/prod/chaitubigfile01.dbf         YES

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME