General
cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-02142: missing or invalid ALTER TABLESPACE option

SOLVED
Go to solution
phillipsp_acca
Occasional Advisor

ORA-02142: missing or invalid ALTER TABLESPACE option

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
4 REPLIES
TwoProc
Honored Contributor
Solution

Re: ORA-02142: missing or invalid ALTER TABLESPACE option

"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.
We are the people our parents warned us about --Jimmy Buffett
Marcel Boogert_1
Trusted Contributor

Re: ORA-02142: missing or invalid ALTER TABLESPACE option

Try this:

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

Regards, MB.
phillipsp_acca
Occasional Advisor

Re: ORA-02142: missing or invalid ALTER TABLESPACE option

Thanks the:

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

Worked

phillipsp_acca
Occasional Advisor

Re: ORA-02142: missing or invalid ALTER TABLESPACE option

Thanks