Rename Oracle table

Question

What is the difference between ALTER TABLE RENAME statement and RENAME TABLE statement.

ie, between

Alter table old_table_name rename to new_table_name

and

rename table old_table_name to new_table_name.

Solution

rename table old_table_name to new_table_name.

That syntax is wrong. there is no table keyword required. The correct syntax is -

rename old_table_name to new_table_name;

Now, let's see where the difference is between alter statement and simple rename statement.

I have two schemas, SCOTT and LALIT.

SQL> SHOW USER
USER is "SCOTT"
SQL>
SQL> create table t(id number);

Table created.

SQL> rename t to t_new;

Table renamed.

SQL> alter table t_new rename to t_newer;

Table altered.

So, both the statements works in the same schema.

Let's connect to the other schema -

SQL> SHOW USER
USER is "LALIT"
SQL>
SQL> create table t(id number);

Table created.

SQL> rename scott.t_newer to t_newest;
rename scott.t_newer to t_newest
       *
ERROR at line 1:
ORA-01765: specifying owner's name of the table is not allowed


SQL> alter table scott.t_newer rename to t_newest;

Table altered.

So, you see the error ORA-01765: specifying owner's name of the table is not allowed. That's where simple rename statement fails on other schema objects. Only ALTER statement works.