How to Rename Table in Oracle and DB2

There are occasions where you can change existing table name to a new name. In the olden days somebody has given incorrect table name, where the name confuses to many developers.

Data is already present in the table. So, you want to change its name. It is possible without affecting existing data with RENAME clause.

I am giving rules and sample SQL query for both ORACLE and DB2 databases.

ORACLE database

Using RENAME you can rename a table to new name.

Checkout DROP and TRUNCATE here.

Sample SQL:

RENAME myvendors to vendor;

Note: You need to take care or re-built about all the constraints that used the old Table name.

Points to remember

  • You cannot rollback the RENAME
  • Indexes, Integrity constraints and permissions automatically apply to a new table
  • All the other objects(Views, Store procedures and Functions) will be invalid, since they use old table name
  • You can rename table, view, sequence, or private synonym
RENAME in two popular databases

DB2 database

The syntax for RENAME in DB2 also same. But, There are some critical rules to follow for renaming a table.


This is also possible to rename a index


Rules to remember

  • All the catalog tables updated with new table name
  • All the indexes transferred to new table
  • The objects that dependent on old table will be invalidated.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.