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.

  • Is the file the only file of a bigfile tablespace? If so, I think you need to specify this using alter tablespace. – Colin 't Hart Jan 28 '14 at 12:46
  • Can you post the exact error in the question? – Phil Sumner Jan 28 '14 at 14:33
  • Yeah, i want to resize two big datafiles of the same tablespace. And the undo tablespace. – Jorge Vega Sánchez Jan 28 '14 at 14:38

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.