How to RENAME TABLE in Oracle

Why would you rename a table?

Renaming clarifies table purpose after schema changes, fixes naming typos, or aligns tables with new business terminology without data migration.

How do I rename a table in Oracle?

Use either the dedicated RENAME statement or the ALTER TABLE ... RENAME TO clause. Both instantly update the data dictionary and keep data intact.

What is the basic syntax?

Syntax options:
• RENAME old_table TO new_table;
• ALTER TABLE old_table RENAME TO new_table;

Will indexes, constraints, and triggers break?

No. Oracle automatically repoints all dependent objects. Only code that hard-codes the table name (views, PL/SQL, application code) must be updated.

Practical example with ecommerce tables

Suppose you want clearer naming by turning orderitems into order_items. The command executes in milliseconds and requires no downtime.

ALTER TABLE orderitems RENAME TO order_items;


Best practices for safe renaming

1. Check for hard-coded references in views, procedures, and application code.
2. Rename during low traffic periods.
3. Keep a rollback script: simply run the reverse rename if needed.
4. Update documentation and data catalogs immediately.

Common mistakes and fixes

Incorrect schema prefix: Omitting schema yields ORA-00942 errors. Prefix the table if you are not in its schema: ALTER TABLE sales.orderitems RENAME TO order_items;

Using quotes inconsistently: Case-sensitive quoted identifiers require matching quotes later. Prefer unquoted identifiers to avoid confusion.

Does renaming require additional privileges?

You need ALTER privilege on the table or the ALTER ANY TABLE system privilege. Verify with USER_TAB_PRIVS before running the command.

How to verify the rename succeeded?

Query USER_TABLES or ALL_TABLES to confirm the new name exists and status is VALID.

Can I rename multiple tables at once?

No single statement supports bulk renames. Use a batch script containing one rename statement per table.

Is a rename undoable?

Yes. Run the opposite rename. Because the operation is DDL, it commits automatically; plan rollback scripts upfront.

Further reading

See Oracle Database SQL Language Reference → RENAME statement.