s counterintuitive as it is, Oracle databases have no SQL command for assigning a new name to a table column yet they have such a command for renaming an entire table. So, when I peruse Oracle database discussion groups, I frequently see the question "how do I rename a table column in my database?"
An Oracle developer may work on a project that requires table columns to have different names from the ones initially assigned during the database design phase. If you're faced with this situation, a simple technique can be your salvation. You can take the following six steps to change table structure attributes, including table columns:
- Drop all the referential integrity (RI) constraints on other tables that refer the target table.
- Rename the target table using some temporary name (e.g., from Emp to Emp001).
- Create the Emp table with a new structure.
- Copy the data from Emp001 to Emp.
- Recreate all the RI constraints that were previously dropped.
- Drop table Emp001.
The danger with this technique is if you make a mistake in just one of the steps, your database will have a broken structure. For example, copying a large amount of data from Emp001 to Emp could fail because of insufficient rollback segment space. Emp001 would be left in the database and Emp would get only partially populatedor even be empty.
In 8i, Oracle introduced a new feature that allows developers to drop the table column, which simplified the above technique to only five steps:
- Drop all constraints and indexes that depend on the column to be renamed.
- Add the new column with the desired name.
- Move the data from the old column to a new column.
- Recreate all the constraints and indexes using a new column.
- Drop the old column.
Still, shouldn't there be an easier way that doesn't call for data manipulation? After all, you just want to rename the column and leave the data as is.
How do I rename a table column in my Oracle database without going through the potentially time-consuming process of dropping and recreating the whole table's data?
As a SYS user, you could go directly to the Oracle data dictionary col$ and modify the column name by updating the corresponding row in the col$ table. However, using the RenameColumn procedure hides the complexity of this process and automatically does the job with proper error handling.