ORA-02140
There're 2 types of ORA-02140 when we tried to add a data file to a tablespace.
1. Use a Keyword as Tablespace Name
We misspelled USERS as USER for the tablespace name.
SQL> alter tablespace user add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users02.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace user add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users02.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-02140: invalid tablespace name
2. Forgot to Specify Tablespace Name
We didn't specify a valid tablespace name between TABLESPACE and ADD.
SQL> alter tablespace add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users02.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users02.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-02140: invalid tablespace name
The error complained about the Oracle reserved keyword USER in the first case or ADD in the second case, you may mistakenly use it in the statement.
ORA-02140 means that SQL parser found a keyword where should be a tablespace name, we should correct the statement with correct syntax.
Solution
The formal way to solve ORA-02140 is to remove the reserved keyword and use a valid tablespace name.
In the first case, USER is a reserved keyword which cannot be used for an identifier, but USERS is a valid tablespace name in the database. We misspelled it in the first place.
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users02.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace altered.
For the second case, you should specify a valid tablespace in the statement.