Rename table, create the same table -> ORA-00955: name is already used by an existing object-CodePudding

I need to create table again, so I did the following:

RENAME DOCUMENT TO DOCUMENT_TO_DROP;

ALTER INDEX DOCUMENT_I  RENAME TO DOCUMENT_I_XXX;
ALTER INDEX DOCUMENT_II RENAME TO DOCUMENT_II_XXX;
ALTER INDEX DOCUMENT_PK RENAME TO DOCUMENT_PK_XXX ;

Then:

  CREATE TABLE "A"."DOCUMENT" 
   (    "SID" NUMBER(15,0),  .....

However this end with error:

ORA-00955: name is already used by an existing object.

but

SELECT *
FROM dba_objects
WHERE object_name = upper('DOCUMENT');

-> no rows!

after this i go back to original state

RENAME DOCUMENT_TO_DROP TO DOCUMENT;

-> no error all good.

How is this possible ?

CodePudding user response：

Can't tell, it isn't obvious at first sight.

Though, I noticed that you're enclosing names into double quotes. My suggestion is NOT to do that.

This is pretty much useless:

SELECT *
FROM dba_objects
WHERE object_name = upper('DOCUMENT');  --> DOCUMENT already is in upper case

Should've been

where upper(object_name) = 'DOCUMENT'

What happens if you run such a query?

I tried to do the same, to see what causes the error:

SQL> create table document
  2    (id     number constraint pk_doc primary key,
  3     id_2   number
  4    );

Table created.

SQL> create index document_i on document(id_2);

Index created.

SQL> rename document to document_to_drop;

Table renamed.

SQL> alter index document_i rename to document_i_xxx;

Index altered.

At first I though it might be because of a constraint, but - nope, error code is different:

SQL> create table document
  2    (id     number constraint pk_doc primary key,
  3     id_2   number
  4    );
  (id     number constraint pk_doc primary key,
                            *
ERROR at line 2:
ORA-02264: name already used by an existing constraint

Is it a table? Seems so, it raises ORA-00955:

SQL> create table document_to_drop
  2    (id     number constraint pk_do2c primary key,
  3     id_2   number
  4    );
create table document_to_drop
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

Perhaps it is some kind of a bug in Oracle. Which version do you use?

Recently, I had 2 database links with exactly the same name; when I tried to drop any of them, I got "ORA-03113: end-of-file on communication channel" error. DBA bounced the database and it "solved" the problem. Can you do that? I'm not saying that it'll actually help (besides, your problem is different than mine) but - if nothing else helps, try it.

CodePudding user response：

You will see this if your table definition includes a nested table. From comments it seems that it does (but showing the full create statement would still be helpful).

As a simple extension of what you have shown, if I define a table type and include that in the table definition:

create table document (
  sid number(15,0),
  nt_col nt_type
)
nested table nt_col store as nt_col_nested_tab;

then I get the same error you do:

rename document to document_to_drop;

create table document (
  sid number(15,0),
  nt_col nt_type
)
nested table nt_col store as nt_col_nested_tab;

ORA-00955: name is already used by an existing object

Depending on how it was defined (when it's a table of objects or of a scalar data type), you might see the nested table name in dba_tables, but it might only appear in dba_nested_tables:

select table_name from user_tables

TABLE_NAME
DOCUMENT_TO_DROP

select table_name from user_nested_tables

TABLE_NAME
NT_COL_NESTED_TAB

So the problem is that you are trying to create a second nested table with the same name. You can rename that as well:

rename nt_col_nested_tab to nt_col_nested_tab_to_drop;

and then the recreation will work:

create table document (
  sid number(15,0),
  nt_col nt_type
)
nested table nt_col store as nt_col_nested_tab;

and you will see the old and new parent and nested tables in the data dictionary:

select table_name from user_tables

TABLE_NAME
DOCUMENT_TO_DROP
DOCUMENT

select table_name from user_nested_tables

TABLE_NAME
NT_COL_NESTED_TAB_TO_DROP
NT_COL_NESTED_TAB

fiddle

If you change your mind and want to revert the name of the original table you will also probably want to revert the name of the nested table as well.

You might also want to drop the table and use flashback table ... to before drop (docs) to recover it; but test that and make sure flashback is enabled and suitably configured before you do anything drastic...