Wednesday, June 06, 2007

ORA-32773 and autoextend on/off

On oracle XE (but may be also on other 10g editions i presume), if you execute:

ALTER TABLESPACE demo AUTOEXTEND ON;
or
ALTER TABLESPACE demo AUTOEXTEND OFF;

You can get:

ORA-32773: operation not supported for smallfile tablespace DEMO

However, you can still turn autoextend on or off by issuing a more traditional:

ALTER DATABASE DATAFILE 'G:\demo01.dbf' AUTOEXTEND OFF;

If you create tablespace demo with the BIGFILE option:

CREATE BIGFILE TABLESPACE demo
DATAFILE 'G:\demo01.dbf' SIZE 100M AUTOEXTEND ON;

then you can turn autoextend on or off at will using the alter tablespace command above.

This behavior is consistent with the traditional architecture of Oracle, where a tablespace may have more than one datafile attached and each datafile could have a different autoextend setting. With the "new" bigfile tablespace type instead there is a single datafile, so there is no confusion as to which datafile should be made autoextensible or not.

Indeed, if you want to know if a tablespace can be autoextended, then you'll have to look at the underlying datafiles:

select tablespace_name, file_name, autoextensible
from dba_data_files
where tablespace_name = 'DEMO';

For a comparison between BIGFILE and SMALLFILE (traditional) tablespaces, see the Oracle XE SQL Reference Guide or the Oracle 10GR1 SQL Reference Guide.

See message translations for ORA-32773 and search additional resources.

3 comments:

Aimee said...

Awesome post, it solved my problem (the DBA's away and I had to extend the tablespace). :)

Byte64 said...

I'm happy that you solved the problem, that's what oraclequirks site is all about...

Thank you

Anonymous said...

Still happens in 11g

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles