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:

1. Drop all the referential integrity (RI) constraints on other tables that refer the target table.
2. Rename the target table using some temporary name (e.g., from Emp to Emp001).
3. Create the Emp table with a new structure.
4. Copy the data from Emp001 to Emp.
5. Recreate all the RI constraints that were previously dropped.
6. 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 populated?or 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.

create table EmpTest asselect * from Emp;

Now add a primary key on the EMPNO column and a unique constraint on the ENAME and SAL columns:

alter table EmpTestadd constraint EmpTest_PKprimary key (empNo);alter table EmpTestadd constraint EmpTest_AK1unique (eName, sal);

To get the structure of the EmpTest table, issue the following command:

desc EmpTest

Name                              Null?    Type——————————— ——– ————-EMPNO                                      NUMBER(4)ENAME                                      VARCHAR2(10)JOB                                        VARCHAR2(9)MGR                                        NUMBER(4)HIREDATE                                   DATESAL                                        NUMBER(7,2)COMM                                       NUMBER(7,2)DEPTNO                                     NUMBER(2)

Because SAL name is not descriptive enough, rename this column SALARY. Reconnect as SYS user before issuing any SQL commands, because later you will go to the Oracle data dictionary and you’ll need to be a SYS user to have the necessary privileges.

Step 1: Identify All Database Objects That Depend on the Column You Plan to Rename
First, find out if the SAL column is part of any constraints of the EMPTEST table in the SCOTT schema:

select t.constraint_name,       t.constraint_type as type,        c.column_name,        c.position from   dba_constraints  t,        dba_cons_columns c where  t.table_name      = ‘EMPTEST’ and    t.owner           = ‘SCOTT’ and    t.constraint_name = c.constraint_name and    t.table_name      = c.table_name and    t.owner           = c.ownerand    t.constraint_name in (select c.constraint_name                              from   dba_constraints  t,                                     dba_cons_columns c                             where  t.table_name      = ‘EMPTEST’                             and    t.owner           = ‘SCOTT’                             and    c.column_name     = ‘SAL’                             and    t.constraint_name = c.constraint_name                             and    t.table_name      = c.table_name                             and    t.owner           = c.owner)order by c.constraint_name,          c.position

Listing 1 shows the output of the above code:

CONSTRAINT_NAME            TYPE  COLUMN_NAME           POSITION————————– —– ——————— ——–EMPTEST_AK1                U     ENAME                        1EMPTEST_AK1                U     SAL                          2 

Now you know that the SAL column is a part of the EMPTEST_AK1 unique constraint. You need to drop this constraint before renaming the column.

Determine Whether the Column Has Constraints or Indexes
To find out if this column is part of any indexes, the following SQL statement:

select index_name,       column_name,       column_position as position       from   dba_ind_columnswhere  table_owner = ‘SCOTT’and    table_name  = ‘EMPTEST’and    index_name in (select index_name                      from   dba_ind_columns                      where  table_owner = ‘SCOTT’                      and    table_name  = ‘EMPTEST’                        and    column_name = ‘SAL’)order by index_name,         position

Listing 2 shows the output of the above code:

INDEX_NAME                 COLUMN_NAME                  POSITION————————– —————————- ——–EMPTEST_AK1                ENAME                               1EMPTEST_AK1                SAL                                 2

In this case, only one index EMPTEST_AK1 depends on the SAL column. the database implicitly creates this index to support the EMPTEST_AK1 unique constraint, and it will be dropped when you drop the constraint.

Find Out If Any Views Depend on the EMPTEST Table
You need to check if any views are based on the EMPTEST table and whether they depend on the SAL column. The following SQL command will verify the first part:

select view_namefrom   dba_viewswhere  owner = ‘SCOTT’;

In order to see if any views actually depend on the SAL column, use the TEXT column of DBA_VIEWS in the SQL WHERE clause. Unfortunately, you can’t do it directly in SQL because the TEXT column’s type is LONG. However, you can find out using the following simple PL/SQL block:

begin   for rec in (select view_name,                      text               from   dba_views               where  owner = ‘SCOTT’)   loop      if instr(upper(rec.text), ‘SAL’) > 0      then         dbms_output.put_line(rec.view_name);      end if;   end loop;end;

In this case, the block does not generate any output, because no views were defined on the EMPTEST table.

Verify Whether the SAL Column Is Used in Any PL/SQL Code
You have to check if the SAL column is used in any PL/SQL code. The following query gives all the occurrences of the word SAL in the TEXT column that contains the code of a PL/SQL unit, which is identified in the NAME column:

select name,       textfrom   dba_sourcewhere  owner = ‘SCOTT’and    upper(text) like ‘%SAL%’

Then you have to look at each row to determine if it really uses the SAL column of your EMPTEST table, and decide whether you need to change the code.

You’ve determined that the only database object that depends on the EMPTEST.SAL column is the unique constraint EMPTEST_AK1. Drop it now; you’ll recreate it later:

alter table SCOTT.EmpTestdrop constraint EmpTest_AK1;

select user_idfrom   dba_userswhere  username = ‘SCOTT’;

The output for the above query is:

  USER_ID———       35

Next, use the USER_ID you just retrieved to find the OBJ# for the EMPTEST table from the OBJ$ Oracle dictionary. The following SQL statement will do the trick:

select obj#,       namefrom   obj$where  name = ‘EMPTEST’and    owner# = 35;

The output of the above query is:

      OBJ# NAME———- ——————-     24862 EMPTEST

Using OBJ# (24862) for EMPTEST table, you could drill down to find the COL# of the SAL column:

select col#,       namefrom   col$where  obj# = 24862and    name = ‘SAL’;

The output of the above query is:

     COL# NAME——— ——————-        6 SAL

Now, you are ready to update the row in the COL# Oracle dictionary, which corresponds to the SAL column (COL# = 6) of the EMPTEST table (OBJ# = 24862) in the SCOTT schema:

update col$set    name = ‘SALARY’where  obj# = 24862and    col# = 6;commit;

Step 3: Package All the Code in the RenameColumn Procedure
Obviously, remembering all the names with # and $, as well as all the commands you issued in Step 2 is a lot to handle. Moreover, since you update the Oracle dictionary directly, you have no room for error in this process. So putting all that code inside one procedure that has proper error handling and optimized SQL makes a lot of sense. With the RenameColumn procedure, all four SQL statements in Step 2 can be combined into one UPDATE statement.

Listing 3 shows all the code for the RenameColumn procedure.

The RenameColumn procedure accepts four self-descriptive parameters:

begin   RenameColumn(‘SCOTT’, ‘EMPTEST’, ‘SAL’, ‘SALARY’);end;

@C:oracleora81RDBMSADMINcatalog.sql;

Also, you must update the data dictionary cache in case the table’s definition was already cached. To do this, flush the shared pool:

alter system flush shared_pool;

Now you can log on to the database via SQL*Plus as SCOTT/TIGER user and check on the structure of the EMPTEST table:

desc EmpTest

The output is:

Name                              Null?    Type——————————— ——– ————-EMPNO                                      NUMBER(4)ENAME                                      VARCHAR2(10)JOB                                        VARCHAR2(9)MGR                                        NUMBER(4)HIREDATE                                   DATESALARY                                     NUMBER(7,2)COMM                                       NUMBER(7,2)DEPTNO                                     NUMBER(2)

As you can see, column SAL has been successfully renamed to SALARY.

Step 5: Recreate All Previously Dropped Database Objects
The only step left is to recreate all the database objects that were dependent on the EMPTEST.SAL column and that dropped during Step 1. Recreate the unique constraint EmpTest_AK1 you dropped:

alter table EmpTestadd constraint EmpTest_AK1unique (eName, salary);

That’s it! You’ve renamed a column quickly and without having to manipulate the data in that table. The only potentially time-consuming process when you deal with large tables is recreating indexes.