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 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:
This produces the following list of the table’s columns:
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:
Step 2: Rename the Column Directly in the Oracle Dictionary
alter table SCOTT.EmpTestdrop constraint EmpTest_AK1;
Use the following code to find the user_id for the SCOTT user:
select user_idfrom dba_userswhere username = ‘SCOTT’;
The output for the above query is:
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:
The first part of the procedure performs the following five checks:
If any of these checks fail, the corresponding exception is raised. Otherwise, the procedure updates the row in the col$ Oracle dictionary using the UPDATE statement with multiple nested subqueries.
Once that’s done, you are ready to change the column name from SAL to SALARY using the RenameColumn procedure:
begin RenameColumn(‘SCOTT’, ‘EMPTEST’, ‘SAL’, ‘SALARY’);end;
The final step is to update the Oracle dictionaries by running Oracle’s catalog.sql script:
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:
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.