Skip to content

How Oracle Rename Table

  • by

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

  1. Table Rename by SQL Statement
  2. Table Rename by GUI Tools

A. Table Rename by SQL Statement

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

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

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

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.

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

  1. SQL Developer Rename Table
  2. PL/SQL Developer Rename Table
  3. Toad for Oracle Rename Table

1. SQL Developer Rename Table

It shows up a menu when we right-click on the table, and we click "Rename Table" function.

SQL Developer - Rename Table Menu

SQL Developer - Rename Table Menu

A dialog prompts to you and asking for a new name.

SQL Developer - Rename Table Dialog

SQL Developer - Rename Table Dialog

The result is shown as below.

SQL Developer - Renamed Table - Indexes

SQL Developer - Renamed Table - Indexes

As we can see, all dependent indexes are transferred to the renamed table.

2. PL/SQL Developer Rename Table

It shows up a menu when we right-click on the table, and we click "Rename Table" function.

PL/SQL Developer - Rename Table Menu

PL/SQL Developer - Rename Table Menu

A dialog prompts to you and asking for a new name.

PL/SQL Developer - Rename Table Dialog

PL/SQL Developer - Rename Table Dialog

The result is shown as below.

PL/SQL Developer - Renamed Table - Result

PL/SQL Developer - Renamed Table - Result

3. Toad for Oracle Rename Table

It shows up a menu when we right-click on the table, and we click "Rename Table" function.

Toad for Oracle - Rename Table Menu

Toad for Oracle - Rename Table Menu

A dialog prompts to you and asking for a new name.

Toad for Oracle - Rename Table Dialog

Toad for Oracle - Rename Table Dialog

The result is shown as below.

Toad for Oracle - Renamed Table - Indexes

Toad for Oracle - Renamed Table - Indexes

Leave a Reply

Your email address will not be published. Required fields are marked *