Table already exists, with Error ORA-00955 reported

This topic describes how to handle Error ORA-00955, where a table to be created already exists in the Oracle mode of OceanBase Database.

Symptom

When you create a table named student, an error is reported, indicating that the table already exists.

obclient [SYS]> CREATE TABLE student(
          id NUMBER,
          name varchar(18),
          sex char(1),
          age NUMBER,
          address varchar(200),
           email varchar(100),
           c_date date,
           PRIMARY KEY (id)
           );
ORA-00955: name is already used by an existing object

The error codes corresponding to this error message are as follows:

• Error code: ORA-00955

• Error codes in OceanBase Database: 5331 and 5834

• SQLSTATE: HY000

For more information about the error codes, see Overview of error codes.

Possible causes

When you create a table, the system reports this error when the table already exists.

Troubleshooting procedure

Take the following steps to check whether a table already exists, rename the existing table, and then re-create the table.

1. Check whether this table already exists in the database. For example:

    obclient [SYS]> SELECT t.table_name tablename FROM user_tables t WHERE table_name = 'student';
   +------------+
   | TABLENAME  |
   +------------+
   | STUDENT    |
   +------------+
   1 row in set
   

2. If the table already exists, check whether this table meets the business needs. If no, we recommend that you specify a new name for the new table, such as t_stu.

    obclient [SYS]> CREATE TABLE t_stu(
          t_id NUMBER,
          t_name varchar(18),
          t_sex char(1),
          t_age NUMBER,
          t_address varchar(200),
          t_email varchar(100),
          t_c_date date,
          PRIMARY KEY (t_id)
           );
   Query OK, 0 rows affected