How to rename a table with ALTER TABLE in Oracle?




Syntax

To rename a table in Oracle, it’s quite similar to other databases:

ALTER TABLE table_name
  RENAME TO new_table_name;

Note that after you rename the table, all foreign key that references to the old table name will be invalid, so are the views, stored procedures, function, and synonyms. So to find all these invalid objects to keep them informed about the new table name, execute this query:

SELECT
  owner,
  object_type,
  object_name
FROM
  all_objects
WHERE
  status = 'INVALID'
ORDER BY
  object_type,
  object_name;

Then update all objects with the new table name.

Example

To rename table employees to company:

ALTER TABLE employees
  RENAME TO company;

And it’s done!

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS