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