Square DBA – Oracle DBA Talk

Renaming a table in Oracle is a very simple process, but you do need to be aware of the results. When you rename a table objects like indexes do move with the rename. However be aware that references to the table from things like PL/SQL block do not. Objects that reference the table will probably be invalid after you rename the table. Here is the syntax for renaming the table:

alter table
   table_name
rename to
   new_table_name;

If you are renaming a table in another user’s schema be aware that you should not use the schema name in the new table name. If you do you will get an ORA-14047 error. This syntax is not correct.

alter table
   scott.table_name
rename to
   scott.new_table_name;

Running this will generate a “ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations” error.

However this is the correct syntax and will run just fine.

alter table
   scott.table_name
rename to
   new_table_name;