There are various ways. The way I prefer to do it is as such (it requires the least effort on my part and on the part of the database. it is the least error prone. it is easily undone):

o you have a table T with a column C. You would like C to be called NewName from now on:

1) rename t to t_table
2) create view t as select ..., C newName, ... from T
3) select 'grant ' || privelege || ' on T to ' || grantee||';'
from user_tab__privs
where table_name = 'T_TABLE';

spool and save the results of that query, run those grants
4) select 'revoke ' || privelege || ' on T_TABLE from ' ||
grantee||';'
from user_tab__privs
where table_name = 'T_TABLE';

spool and save the results of that query, run those revokes

As far as anyone is concerned, the column has been renamed with minimal IO and no impact on future performance.



o you have the same situation as above. You have Oracle8i or up. you could:

1) alter table T add newName <datatype>;
2) update t set NewName = C, C = Null;
3) alter table T set unused column C; -- flag delete, since
C is null everywhere, no real need to rewrite the entire
table by dropping C, flag deletion is more then sufficient


o you have the same situation. You have Oracle8.0 and before. Your options (other then the view) are somewhat limited. You can create a copy of the table as select (renaming the column on the way into the new table), dropping the original table and then renaming the copy back -- but you must restore the grants, triggers, constraints, etc. If you take an export of the table with rows=N before you do the copy and then import the table AFTER you rename the copy, it'll get most of that stuff back for you. Here is an full annotated example using that method:

Here is our table. Our goal will be ultimately to rename Y to "newName"

ops$tkyte@8i> create table t
2 ( x int primary key,
3 y int check ( y > 0 ) );
Table created.

ops$tkyte@8i>
ops$tkyte@8i> create or replace trigger t_trigger
2 before insert on t for each row
3 begin
4 :new.y := :new.y+1;
5 end;
6 /

Trigger created.

ops$tkyte@8i> grant select on t to connect;
Grant succeeded.

ops$tkyte@8i> grant insert on t to resource;
Grant succeeded.

ops$tkyte@8i> create index y_idx on t(y);
Index created.

ops$tkyte@8i> select object_name, object_type
2 from user_objects
3 union all
4 select constraint_name, 'CONSTRAINT'
5 from user_constraints
6 union all
7 select privilege, grantee
8 from user_tab_privs
9 /

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
SYS_C0025444 INDEX
T TABLE
T_TRIGGER TRIGGER
Y_IDX INDEX
SYS_C0025443 CONSTRAINT
SYS_C0025444 CONSTRAINT
SELECT CONNECT
INSERT RESOURCE

8 rows selected.

So, there are the things about T we need. We have some indexes, triggers, constraints, grants...

ops$tkyte@8i> host exp userid=/ tables=t rows=n

Export: Release 8.1.5.0.0 - Production on Thu Jun 15 09:13:11 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table T
Export terminated successfully without warnings.

That export got what we needed structurally about T. It extracted only DDL, no data

ops$tkyte@8i> create table t2 as select x x, y newName from t;
Table created.

That "renamed" the column, we can now:


ops$tkyte@8i> drop table t;
Table dropped.

ops$tkyte@8i> rename t2 to t;
Table renamed.

So, effectively the column has been renamed, now we need to get back all of the "stuff" about T from above (constraints, grants, etc)

ops$tkyte@8i> host imp userid=/ full=y ignore=y

Import: Release 8.1.5.0.0 - Production on Thu Jun 15 09:13:15 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Export file created by EXPORT:V08.01.05 via conventional path
import done in US7ASCII character set and US7ASCII NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
IMP-00017: following statement failed with ORACLE error 904:
"CREATE INDEX "Y_IDX" ON "T" ("Y" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR"
"AGE(INITIAL 524288) TABLESPACE "UTILS" LOGGING"
IMP-00003: ORACLE error 904 encountered
ORA-00904: invalid column name
IMP-00017: following statement failed with ORACLE error 904:
"ALTER TABLE "T" ADD CHECK ( y > 0 ) ENABLE"
IMP-00003: ORACLE error 904 encountered
ORA-00904: invalid column name
IMP-00041: Warning: object created with compilation warnings
"CREATE TRIGGER "OPS$TKYTE".t_trigger"
"before insert on t for each row"
""
"begin"
" :new.y := :new.y+1;"
"end;"
Import terminated successfully with warnings.

So, according to the import,

o the INDEX Y_idx failed (we need to create that, convienently, the create index we need to modify is right there...).

o the check constraint failed, again we have the statement we need to fix right there

o the create trigger failed as well -- we'll have to fix that one up



ops$tkyte@8i> select object_name, object_type
2 from user_objects
3 union all
4 select constraint_name, 'CONSTRAINT'
5 from user_constraints
6 union all
7 select privilege, grantee
8 from user_tab_privs
9 /

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
SYS_C0025445 INDEX
T TABLE
T_TRIGGER TRIGGER
SYS_C0025445 CONSTRAINT
SELECT CONNECT
INSERT RESOURCE

6 rows selected.

ops$tkyte@8i>

but as you can see -- all of the other things (grants and such) are in place for us.