ORA-14047 When trying to rename a table

A convenient way to rename tables in Oracle is to use the RENAME command. For example,

SQL> create table kaleyc.drop_me
  2  (
  3     x char
  4  );

Table KALEYC.DROP_ME created.

SQL> rename drop_me to drop_me2;
 
Table renamed.

However, if the object that you’re trying to rename does not exist in whatever your CURRENT_SCHEMA is currently set to, then you can’t use the RENAME command:

SQL> alter session set current_schema = OTHER_SCHEMA;
 
Session altered.

SQL> rename drop_me2 to drop_me;
 
Error starting at line : 24 in command -
rename drop_me2 to drop_me
Error report -
ORA-04043: object DROP_ME2 does not exist
04043. 00000 -  "object %s does not exist"
*Cause:    An object name was specified that was not recognized by the system.
           There are several possible causes:
           - An invalid name for a table, view, sequence, procedure, function,
           package, or package body was entered. Since the system could not
           recognize the invalid name, it responded with the message that the
           named object does not exist.
           - An attempt was made to rename an index or a cluster, or some
           other object that cannot be renamed.
*Action:   Check the spelling of the named object and rerun the code. (Valid
           names of tables, views, functions, etc. can be listed by querying
           the data dictionary.)

SQL> alter session set current_schema = kaleyc;

Session altered.
 
SQL> rename drop_me2 to drop_me;
 
Table renamed.

To rename a table outside your CURRENT_SCHEMA, you must use the ALTER TABLE (table name) RENAME TO (table name).

But here’s a commonly encountered problem (with an extremely simple solution).

Many people receive an ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations error

The solution is, you cannot specify the schema when referencing the table the second time.

So change from this:

alter table kaleyc.drop_me rename to kaleyc.drop_me2;

…to this:

SQL> alter table kaleyc.drop_me rename to drop_me2;

Table KALEYC.DROP_ME altered.

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets