Oracle: How to Rename a Table Using SQL

To change a table name, there are two types of commands available: rename table name command and alter table name command. The alter table command is used to rename the existing table name. The specified table name is the existing name that needs to be renamed. The “rename to” keyword is used to rename the table name, followed by the new table name mentioned in the syntax.

Introduction to Oracle rename table

The table rename feature in Oracle allows users to change the name of an existing table to their desired name. When a table name is renamed, Oracle automatically transfers all associated privileges, including indexes, constraints, and grants from the old table name to the new one. Additionally, Oracle invalidates all objects that rely on the renamed table name, such as synonyms, views, functions, and procedures. The process of renaming table names can be performed using two different commands, namely, the rename table name command and the alter table name command.


Syntax


rename old specified table name to new specified table name;


Explanation

The syntax mentioned above involves the use of the “rename table” command to alter the name of an existing table, view, sequence, or synonym. The old specified table name refers to the current name of the table, while “to” is a keyword used for referencing purposes. The new specified table name is used as the new name for the existing table name, and it must not already be in use for another table.

How to rename a table in Oracle?

Let’s explore the method to change the name of an Oracle table.

There are two ways to rename a table, either by using the rename command or the alter table name command, both of which are simple to use. However, renaming a table requires certain privileges, such as being a database owner or table owner. It’s important to note that a table must already exist in order to perform the rename command, and the syntax for the command has been discussed previously. Additionally, table names must be unique, and if there are any dependencies on the table, we can rename the table name, but not any synonyms that depend on it.


Examples

Below are various instances of renaming tables in Oracle that will aid in comprehending the concept better.

To execute the rename table command, it is essential to have a pre-existing table. Therefore, let’s generate a table using the subsequent statement.


create table product(

product_id int,

product_name varchar(255) not null,

product_cost decimal(15,2) not null,

primary key (product_id));


Explanation

The example demonstrated how to create a new table called “product” using the “create table” command. The table was created with various attributes, as shown in the previous statement. The resulting output can be seen in the snapshot below after executing the query.

Oracle rename table output 1

The statement for renaming can now be executed in the following manner.


rename product to new_product;


Explanation

Using a rename statement, we can rename an existing table. In this scenario, we renamed the old table name, which was “product,” to a new name using the keyword “to.” The new name we chose was “new_product.” When we ran the query, we captured the final output in the following snapshot.

Oracle rename table output 2

Now, let’s explore an alternative method for renaming the table name.


Syntax


alter table specified table name rename to new table name;


Explanation

The syntax shown above utilizes
alter table command
for renaming a table. The specified table name refers to the existing name of the table. To rename the table, the “rename to” keyword is used, followed by the new name of the table, which is also mentioned in the above syntax.


Example

For the purpose of this illustration, we utilized an existing table that was named “new_product”.


alter table new_product rename to product_demo;


Explanation

The example presented involves using the alter table command to rename an existing table. Specifically, the table name “new_product” is being changed to “product_demo”. The output of this query is demonstrated in a snapshot shown below.

Oracle rename table output 3

  • Execute the “insert into” statement to add records to the table, then proceed to rename the table using the appropriate command.

Once you have added entries to the product_demo table, utilize a select statement to view the records.


select * from product_demo;

After running the mentioned query, we present the end result through the snapshot below.

output 4

Execute the rename command to verify the transfer of all constraints to the renamed table.


rename product_demo to product_sample;

Upon running the previous query, view the contents of the recently renamed table in the subsequent manner.


select * from product_sample;

Upon running the aforementioned query, we present the end result through the use of the subsequent snapshot.

output 5


Explanation

Observe the disparity between the table names prior to and after renaming. The screenshot below displays the recorded entries for product_demo.

Rules and regulation for rename table

Let’s examine the various guidelines and restrictions concerning the renaming of table statements.

  1. The table’s new name must be distinct, signifying that the name of the table does not presently exist, or else an error will arise indicating that the name is already in use.
  2. In order to change the name of a table, it is necessary to have the authority to do so. This entails being either the owner of the database or the owner of the table.
  3. We can execute all queries related to data definition language in auto-commit mode. This implies that if we choose to rename a table, it cannot be reverted back.
  4. In case the specified table is referenced by a view or foreign key within the table and we intend to rename it, an error message will be displayed. Similarly, if the table has any constraints or triggers and we try to rename it, an error message will also appear.
  5. In case a cursor that is currently open refers to the mentioned table, the rename statement will not function.
Conclusion

By reading this article, you should have gained a comprehensive understanding of the Oracle rename table. The article covers the fundamental syntax of renaming tables and provides various examples of renaming tables. Additionally, it explains the situations in which the Oracle rename table is used.

Final thoughts

In this article, we will be discussing the process of Oracle table renaming. We will cover the fundamental syntax of renaming tables and provide multiple examples. Additionally, you can refer to the accompanying articles for further insight.

Frequently Asked Questions

Posted in Sql