How Oracle Rename Table

You can either using SQL statement or GUI tools to rename a table, depending on your requirement.

Table Rename by SQL Statement
Table Rename by GUI Tools

Table Rename by SQL Statement

There're 2 kinds of statement that are used to rename a table in Oracle.

ALTER TABLE RENAME TO

Traditional ALTER TABLE statement is able to rename the table.

Login as Owner

You can change the table name by the owner.


SQL> show user

USER is "HR"

SQL> ALTER TABLE employees RENAME TO employees_bak;



Table altered.

Login as Other User

This operation can be done by other user as long as you qualify the table with its owner name for the first one.


SQL> show user

USER is "SYSTEM"

SQL> ALTER TABLE hr.employees RENAME TO employees_bak;



Table altered.

RENAME TO Statement

A simple RENAME TO statement can also change the table name, but you have to login as the object's owner. For example:


SQL> show user

USER is "OE"

SQL> select object_type from user_objects where object_name = 'ORDERS';



OBJECT_TYPE

-----------------------

TABLE



SQL> rename orders to orders_bak;



Table renamed.

Don't worry about external reference constraints, they always follow OBJECT_ID of the table and OBJECT_ID remains the same. However, dependent objects like views and synonyms need to be re-created or replaced.

Rename Table vs. Alter Table Rename

Although the result is the same, they are somewhat different. RENAME TO statement can only rename issuer's own table, whereas ALTER TABLE RENAME TO can rename other's table.

Please note that, you can rename the table no matter if the table is READ ONLY or not.

Table Rename by GUI Tools

Some handy GUI tools can also be used to change the table name. Here we introduce some popular DB connection tools like "SQL Developer", "PL/SQL Developer" and "Toad for Oracle" to rename a table.