How to Rename a Table in Oracle: the Ultimate Guide

Renaming is not an uncommon operation when it comes to database tables. You might want to assign a more meaningful name to a table. You might need to introduce changes that will make multiple table names consistent. Whatever is your situation, we will show you how to rename tables in your Oracle databases.

However, renaming, simple as it may seem, bears several risks that should not be ignored. You need to know what processes and relations involve the required table. You must make sure that your renaming will be reflected in the code that refers to that table, and that other database objects will keep working properly after the renaming.

Still, that’s not much of a problem with a few useful tips from this article and the right tool at hand. Let’s get started!

Contents

1. Oracle RENAME TABLE syntax
2. Table name renaming: rules and restrictions
3. Oracle RENAME TABLE example
4. Renaming multiple tables in Oracle
5. Using ALTER TABLE as a rename command in Oracle
5.1. Renaming a table in another schema
5.2. Using ALTER TABLE to rename partitions
6. How to change the table name in dbForge Studio for Oracle

Oracle RENAME TABLE syntax

The basic statement syntax to rename a table in an Oracle database is as follows:

RENAME TABLE table_name TO new_table_name;

To look up the exact name of the table you need to rename, you can retrieve tables in Oracle by querying the data dictionary views. If you want to show all tables in the entire Oracle database, make a SELECT query from the dba_tables view:

SELECT table_name 
FROM dba_tables;

Now let’s take a look at a few more specific cases. For instance, you can query the user_tables view to retrieve tables that are owned by the current user:

SELECT table_name
FROM user_tables
ORDER BY table_name;

You can query the all_tables view to retrieve tables that are accessible to the current user:

SELECT table_name
FROM all_tables
ORDER BY table_name;

If you want to get all tables of a specific owner, add the OWNER column to the WHERE clause:

SELECT *
FROM all_tables
WHERE OWNER = 'OT'
ORDER BY table_name;

Table name renaming: rules and restrictions

Keep the following rules and restrictions in mind when renaming a table in Oracle:

• You must have the required user privilege to rename a table — you must be either the database owner or the table owner.
• All DDL-related queries are executed in the auto-commit mode; this means once a table is renamed, it cannot be reverted.
• The new name of the required table must be unique; otherwise, you will get an error message stating that the name is already used.
• If there is a constraint or trigger on the required table, the RENAME TABLE statement will not work, and you will get an error message.
• If there is an open cursor that references the required table, the RENAME TABLE statement will not work, and you will get an error message.

Oracle RENAME TABLE example

Now let’s check how to rename a table in Oracle on an example. Let’s keep it short and simple. First, we create a table in an Oracle database:

CREATE TABLE ORDERS (
   "ORDERID"      NUMBER CONSTRAINT PK_ORDERID PRIMARY KEY
  ,"CUSTOMERID"    NUMBER(*, 0) NOT NULL
  ,"ORDERDATE"     DATE NOT NULL 
  ,"SHIPDATE"      DATE NOT NULL
  ,"CUSTOMERNOTES" VARCHAR2 (100)
 ) TABLESPACE USERS
 STORAGE (INITIAL 64 K
          MAXEXTENTS UNLIMITED)
 LOGGING;

Then we can easily rename it:

RENAME TABLE ORDERS TO ACTIVE_ORDERS;

Renaming multiple tables in Oracle

You can rename multiple Oracle tables with a single statement:

RENAME TABLE table_name_1 TO new_table_name_1,
             table_name_2 TO new_table_name_2,
             table_name_3 TO new_table_name_3;

Using ALTER TABLE as a rename command in Oracle

There is an alternative to RENAME TABLE that can also be used to give the required table a new name. It is a statement called ALTER TABLE, and it has the following syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

Please note that you can’t rename multiple tables with a single ALTER TABLE statement.

Renaming a table in another schema

ALTER TABLE can also be used to rename an Oracle table in another schema:

ALTER TABLE owner.table_name RENAME TO new_table_name;

Using ALTER TABLE to rename partitions

You can use ALTER TABLE to rename table and index partitions in Oracle. For instance, you might want to change the default partition name, assigned by the system, to something more meaningful. The syntax is as follows:

ALTER TABLE table_name
RENAME PARTITION partition_name
TO new_name;

You can apply a similar syntax to rename a subpartition:

ALTER TABLE table_name
RENAME SUBPARTITION subpartition_name
TO new_name;

How to change the table name in dbForge Studio for Oracle

dbForge Studio for Oracle is an all-in-one IDE for Oracle database development and administration, and smart renaming of database objects is only one of its facets. Besides tables, you can rename views, stored procedures, functions, triggers, packages, sequences, user types, synonyms, clusters, and materialized views. What is more, dbForge Studio automatically finds and corrects all references to the required object, so you don’t have to waste time seeking them out and correcting them manually.

You can rename an object in a script, not in a database. To do it, take the following steps:

1. Right-click the required object and go to Refactoring > Rename on the shortcut menu. Alternatively, you can select the object and press F2. The object will be highlighted.

2. Type a new name for your object in the SQL editor window. As you type, a tooltip appears instructing you to Press F2 to preview changes or Enter/Tab to apply.

3. Press F2 to open the Preview Changes – Rename dialog and preview code changes.

4. Press Apply to apply changes. Alternatively, press Enter/Tab to apply your changes in the code.

That’s it! As you can see, the rich toolset of dbForge Studio offers the safest and easiest means of refactoring. We have a similar post where you can learn how to rename tables in MySQL. And if you are looking for more Oracle tutorials, we suggest you check how to copy tables in Oracle. Finally, you can take a look at the complete functionality of dbForge Studio.

Download a 30-day free trial of dbForge Studio for Oracle, give it a try, and see how valuable it can become for your own daily database operations.