Question:
How do I rename a table column ?
Answer:
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.
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) ); create view public_view as select col1, col2, clo3 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 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 neededing 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 difference literal values, the overhead should not be terribly significant.
Pre-8.1, the same technique could be applied to 'dropping a column'. Simple 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.
If you don't take the VIEW-based approach, you have to find a way of copying the table very 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 option 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 column names do not match. To effect this trick, we create a one-partition range-partitioned table 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 into 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.