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