Skip to content

How to Resolve ORA-02216: tablespace name expected

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.

  1. CEATE TABLE
  2. ALTER TABLE
  3. CREATE INDEX
  4. ALTER INDEX
  5. CREATE TABLESPACE

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 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 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 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 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.

Leave a Reply

Your email address will not be published.