ORA-14047 and Rename a Table

My manager pinged today, asking for the syntax to rename a table. He said he is on a Crisis Management Call, having issue with renaming table, receiving ORA-14047. I did remember that I had trouble to rename tables when I implemented a partitioning task last year. The trick is when using 'alter' statement the new table name should not be prefixed with the schema name.

Below is my test to repeat the ORA-14047 error.

denis@TESTDB> create table t ( a int);

Table created.


denis@TESTDB> alter table denis.t rename to t12;

Table altered.


denis@TESTDB> alter table denis.t12 rename to denis.t13;
alter table denis.t12 rename to denis.t13
*
ERROR at line 1:
ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations


denis@TESTDB> alter table denis.t12 rename to t13;

Table altered.

denis@TESTDB> rename t13 to t14;

Table renamed.