The Oracle (tm) Users' Co-Operative FAQ

How do you rename a column in a table ?


Author's name: Jonathan Lewis

Author's Email: jonathan@jlcomp.demon.co.uk

Date written: 20th July 2000

Oracle version(s): 8.1.5

From time to time you may decide that a large, populated, table in a production system has an inapproptriate name - it is not unknown, for example, for new words to get onto the reserved word list and become impossible (or at least dangerous) to use a column names. Is there any way in Oracle to change the name of a table without going through a laborious and time-consuming process.

Back to index of questions


The short, and technically correct, answer is that you cannot rename a table column. This means you have two possible strategies to adopt to solve the problem - make it unnecessary ever to rename a column, or find a cheap way of recreating the table with a renamed column. This note covers both options.

There is an alternative which involves hacking at the data dictionary, but this can have serious, and surprising, side-effects, so it is not covered here. Also, the new features docmentation tells that re-naming columns will be possible in Oracle 9i.

Option 1: Avoid renaming a column, ever:

If you always create a view that selects all columns from a table, and only expose the view to client applications, you will be able to recreate the view substituting a changed column name; for example:

        create table base_table (
               col1    number(5),
               col2    number(5),
               clo3    number(5)      -- note the typing error
        );
        
        create view public_view as
        select 
               col1,
               col2,
               clo3                   -- default code generation repeats the error
        from
               base_table;
        
        grant select, insert, update, delete
        on public_view to app_user;
        
        create or replace view public_view as
        select
               col1,
               col2,
               clo3    col3           -- but we have the technology to fix it quickly
        from
               base_table
        ;

When you recreate the view, the privileges granted to role app_user will not be lost, so this single command will effect the change you require without needing follow-up actions. Of course, this isn't strictly true - any PL/SQL object which references the view will become invalid, and will need to be recompiled (although this could happen automatically on next use). More significantly any such PL/SQL which references the column with its original name will need to be corrected and recompiled; so, in general, server-side code that uses the table should reference the table directly, not the view (and in particular you can't write standard DML triggers on views, so these have to reference the table and its real column names)..

In a similar vein, the references privilege is not relevant to views, so if you want another schema to create a table that references (has a foreign key relation to) the base table, then that schema has to know about the actual column names of the table.

The main drawback to this approach is that there is a small parse-time overhead associated with using a view instead of a table; and in early versions of Oracle this was not so small, which is one reason why views tend to get a very bad press. However, if you tend to use bind-variables in your code rather than generating huge numbers of virtually identical SQL statements with different literal values, the overhead should not be terribly significant.

Pre-8.1, the same technique could be applied to "drop"'. Simply re-create the view to eliminate the redundant column; although to be safe it was necessary to ensure that the column had not been declared with a not null constraint. The process of reclaiming space would still be expensive and time-consuming, of course.


Option 2: recreating the table quickly:

If you don't take the view-based approach, you have to find a way of copying the table very quickly and cheaply using a new column name. This can be expensive and time-consuming; moreover, you then have to ensure that all the relevant privileges have been recreated, all the foreign keys re-established and validated, and all appropriate check constraints met.

The commonest approach (omitting all the safety checks in between, especially the one before the drop) is simply:

        create table base_table (
               col1    number(5),
               col2    number(5),
               clo3    number(5)
        );
        
        insert into base_table values (1,1,1);
        
        
        create table new_table 
        nologging
        as
        select
               col1    col1,
               col2    col2,
               clo3    col3
        from base_table 
        ;
        --
        --      Check that it has worked
        --
        
        drop table base_table;
        rename new_table to base_table;
        
        --      Now recreate the indexes, fix the constraints, 
        --      grant privileges, edit and recompile PL/SQL etc.
        

Although the table and its indexes can be recreated with the nologging (or in Oracle 7 unrecoverable) option to reduce the total I/O, this process still has to do a lot of I/O to read the original table and recreate the new one; also if you do use the nologging option you may have to worry about an extra backup or other tasks to deal with a standby database.

Oracle 8 introduces a much less resource-intensive strategy if you have the partitioning option installed. This revolves around the exchange partition feature of partitioned table, which allows you to swap a partition with a table - even if the column names do not match. To effect this trick, we create a range-partitioned table consisting of one-partition with a range value of maxvalue, then swap our base table into it, recreate the base table (with the columns in the same order, but renamed), then swap the partition back out.

A minimalist sample of code would look like the following; again this excludes safety checks, and there are various other details that are version-dependent to get the best performance:

        create table base_table (
               col1    number(5) constraint pk_bt primary key,
               col2    number(5),
               clo3    number(5)
        );
        
        create table pt_table (
               col1    number(5) constraint pk_pt primary key using index local,
               col2    number(5),
               col3    number(5)
        )
        partition by range (col1) (
        partition p1 values less than (maxvalue)
        );
        
        alter table pt_table 
        exchange partition p1 with table base_table
        including indexes without validation;
        
        drop table base_table;
        create table base_table (
               col1    number(5) constraint pk_bt primary key,
               col2    number(5),
               col3    number(5)
        );
        
        alter table pt_table 
        exchange partition p1 with table base_table
        including indexes without validation;
        

As usual, there are a few irritating odds and ends to deal with. In this case the trick works better with 8.0 than 8.1 because of some extra validation that Oracle 8.1 does on the exchange partition. You will find that you have to muck around with disabling and re-enabling constraints (possibly going through the novalidate state), and under 8.1 it is best to represent the primary key constraint through a pre-created non-unique index to to get the maximum efficiency from this trick


Further reading: N/A

This question is also addressed by the following documents:

Author

Title/URL

Suggested by

Referee's comments

Howard Rogers

Renaming Columns

Jonathan Lewis

One of several “discussion documents” on the Dizwell website that take the time and space to examine Oracle features properly.

 

 

 

 

 


Back to top

Back to index of questions