Rename Column in an Oracle Table

Can you change the name of a table column in Oracle?

Oracle 9i and above, you can very easily – there is a direct RENAME column command. Pre-Oracle 9i, you have to do a little more.

In this article, we’ll look at both the current and older methods for how to rename column in an Oracle table.

Say, in a table EMPLOYEE, you want to change column SAL to SALARY.

Rename column: Oracle 9i and above

This is what your table looks like before the change:

SQL> desc employee
 Name              Null?    Type
 ----------------- -------- ------------
 EMPNO                      NUMBER(4)
 ENAME                      VARCHAR2(10)
 JOB                        VARCHAR2(9)
 MGR                        NUMBER(4)
 HIREDATE                   DATE
 SAL                        NUMBER(7,2)
 COMM                       NUMBER(7,2)
 DEPTNO                     NUMBER(2)

One liner to get the job done.

SQL> alter table employee
  2  rename column
  3  sal to salary;

Table altered.

Lo and behold!

SQL> desc employee
 Name              Null?    Type
 ----------------- -------- ------------
 EMPNO                      NUMBER(4)
 ENAME                      VARCHAR2(10)
 JOB                        VARCHAR2(9)
 MGR                        NUMBER(4)
 HIREDATE                   DATE
 SALARY                     NUMBER(7,2)
 COMM                       NUMBER(7,2)
 DEPTNO                     NUMBER(2)

Rename column: Pre Oracle 9i

Steps:

1. Create a temporary table employee_t using CREATE TABLE AS SELECT, with the new column name in place of the old one.
2. Give all grants, indexes, etc. to the new table employee_t, to match with the old one employee.
3. Drop old table employee.
4. Rename employee_t as employee.

See it in action:

SQL> create table employee_t
  2  (empno
  3  ,ename
  4  ,job
  5  ,mgr
  6  ,hiredate
  7  ,salary -- new column name
  8  ,comm
  9  ,deptno
 10  )
 11  as
 12  select * from employee;

Table created.

SQL> desc employee_t
 Name              Null?    Type
 ----------------- -------- ------------
 EMPNO                      NUMBER(4)
 ENAME                      VARCHAR2(10)
 JOB                        VARCHAR2(9)
 MGR                        NUMBER(4)
 HIREDATE                   DATE
 SALARY                     NUMBER(7,2)
 COMM                       NUMBER(7,2)
 DEPTNO                     NUMBER(2)

SQL> drop table employee;

Table dropped.

SQL> rename employee_t to employee;

Table renamed.

SQL> desc employee;
 Name              Null?    Type
 ----------------- -------- ------------
 EMPNO                      NUMBER(4)
 ENAME                      VARCHAR2(10)
 JOB                        VARCHAR2(9)
 MGR                        NUMBER(4)
 HIREDATE                   DATE
 SALARY                     NUMBER(7,2)
 COMM                       NUMBER(7,2)
 DEPTNO                     NUMBER(2)

Summary

This article demonstrates how to rename column in an Oracle table. It also provides a workaround to rename a table column in older versions of Oracle that do not support the RENAME command.