ORA-02216
ORA-02216 means that SQL parser finds no identifier at the position where expects a tablespace name. Basically, it's a syntax error. To correct it, we should specify a valid identifier for the tablespace name.
There're several error types that throw ORA-02216. Most likely, you forgot to specify the tablespace name.
CEATE TABLE
SQL> create table t1 (c1 number) tablespace;
create table t1 (c1 number) tablespace
*
ERROR at line 1:
ORA-02216: tablespace name expected
We should specify a valid tablespace name for the statement.
SQL> create table t1 (c1 number) tablespace example;
Table created.
ALTER TABLE
SQL> alter table t1 move tablespace;
alter table t1 move tablespace
*
ERROR at line 1:
ORA-02216: tablespace name expected
We should specify a valid tablespace name for the statement.
SQL> alter table t1 move tablespace users;
Table altered.
CREATE INDEX
SQL> create index i1 on t1(c1) tablespace;
create index i1 on t1(c1) tablespace
*
ERROR at line 1:
ORA-02216: tablespace name expected
We should specify a valid tablespace name for the statement.
SQL> create index i1 on t1(c1) tablespace example;
Index created.
ALTER INDEX
SQL> alter index i1 rebuild tablespace;
alter index i1 rebuild tablespace
*
ERROR at line 1:
ORA-02216: tablespace name expected
We should specify a valid tablespace name for the statement.
SQL> alter index i1 rebuild tablespace users;
Index altered.
CREATE TABLESPACE
When you forgot to specify tablespace name in CREATE TABLESPACE statement, you got another error ORA-02180 instead of this error. So, let's see another use case that throw ORA-02216 in CREATE TABLESPACE statement.
SQL> create tablespace add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/tbs01.dbf' size 10m autoextend on next 10m maxsize unlimited;
create tablespace add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/tbs01.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-02216: tablespace name expected
The error complained about the Oracle reserved keyword ADD in the statement, you may mistakenly use it in the statement. In fact, most Oracle reserved keywords come from ANSI SQL reserved keywords.
Solution
The formal way to solve ORA-02216 is to remove the reserved keyword and use a valid tablespace name.
In this case, ADD is a reserved keyword which cannot be used for an identifier, you should use a valid tablespace name for it.
If you really want the reserved keyword to be a tablespace name, please use quotations to wrap the identifier.
SQL> create tablespace "add" datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/tbs01.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
Don't use a reserved keyword as an identifier unless you have a good reason to do so.