ORA-02142: missing or invalid ALTER TABLESPACE option

[phillipsp_acca]

Hi,

I am trying to increase a tablespace which is reporting back 'ORA-02142: missing or invalid ALTER TABLESPACE option' when i issue try to resize the database.

TABLESPACE_NAME CONTENTS TBS_MBYTES USED_MBYTES FREE_MBYTES FREE_PERCENT TYPE
------------------------------ ---------- ---------- ----------- ----------- ------------ -------
XDB PERMANENT 38 37.88 0.25 0.66 PERCENT

SQL> select * from dba_data_files where tablespace_name = 'XDB';

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------
USER_BYTES USER_BLOCKS
---------- -----------
/u01/app/oracle/oradata/OEMREP1/xdb01.dbf
9 XDB 39976960 9760 AVAILABLE 9 YES 1.7180E+10 4194302 160
39911424 9744


SQL> alter tablespace XDB
2 datafile '/u01/app/oracle/oradata/OEMREP1/xdb01.dbf' resize 50M;
datafile '/u01/app/oracle/oradata/OEMREP1/xdb01.dbf' resize 50M
*
ERROR at line 2:
ORA-02142: missing or invalid ALTER TABLESPACE option

Any idea without bringing offline.

Regards,
Pat

[TwoProc · Accepted solution]

"resize 50M;"

should be "resize 50M,"

Also, what's the "2" at the beginning of the second line, if that's not just a posting artifact, it needs to be cleaned up.

[Marcel Boogert_1]

Try this:

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/OEMREP1/xdb01.dbf' RESIZE 50M;

Regards, MB.

[phillipsp_acca]

Thanks the:

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/OEMREP1/xdb01.dbf' RESIZE 50M;

Worked

[phillipsp_acca]

Thanks