Oracle 10g – How to Resize Tablespace

alter-databaseoracle-10gtablespaces

I'm trying to resize a couple of tablespaces but the following query is only valid in Oracle 11g.

alter tablespace 2 DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

In oracle v10g i try with this query, picked from oracle documentation :

alter DATABASE DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

I work with Oracle 10g and this query throws error message according to a non valid ALTER DATABASE modifier.

Error messages:
ERROR at line 1:
ORA-02140: invalid tablespace name

Thanks in advance.

Best Answer

Resizing tablespaces/datafiles is not as straightforward as you might intuitively expect. The following post does an amazing job at explaining all the ins and outs, and how to do it easily through Enterprise Manager: http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php

Related Question