Add a column and rename a column

Hi all, I wonder if I can add a column and rename a column to an exisiting table in Oracle 8i. If I can do that, can you please show me how? Many thanks!

you can add a column with no problems
you ca NOT rename a column, you have to drop then add

AFAIK you cannot rename a column. Confirm this w/ someone. To add a column, use the command:
alter table table_name add (column_name datatype);

Nope! You cannot rename a column, but table in 8i.

take the obj#(object number) from obj$ where name=(your object name)
then
update col$ set name=(bla-bla-bla) where
obj#=(you object number) and name=(the actual column name)
after commit run the catalog file
p.s. disable all constraints (and indexes) on that column

Quote:

---

Originally posted by aracnid
take the obj#(object number) from obj$ where name=(your object name)
then
update col$ set name=(bla-bla-bla) where
obj#=(you object number) and name=(the actual column name)
after commit run the catalog file
p.s. disable all constraints (and indexes) on that column

---

I have always been told by Oracle instructors and senior colleagues not to do this, i.e., manually change the data dictionary.

I guess if you know what you are doing, it should be fine.

you can add a column of the appropriate datatype, copy the data from the old column into the new column (use UPDATE), then drop the old column.

Starting with Oracle 8i Release 3 (8.1.7) you can rename a column with an ALTER TABLE command.

aracnid one of my collegue touched data dictionary and messed it up and called Oracle Support, obviously he didnt say he touched the data dictionary but finally somehow the support found out and they simply said

"since the data dictionary has been modified by customer/user, an action which must not done, us Oracle Support we cant do nothing else but suggest customer/user to rebuild their database"

Do you think it's really worth to try it?

This modifying the data dictionary issue was also being discussed the other day in another forum when they were talking about renaming a tablespace by updating ts$

Hi, Jmodic,

What is the syntax.

Thanks

Sory, I must excuse myself. I just checked the documentation, and there is no such thing as directly renaming a column in 8.1.7. I know I've read about this new SQL enhancement, I thought it was about 8i rel.3, but it obviously must have been a white paper of new features of 9i.

So until then, we will have to stick with that clums CREATE_NEW_COL -> POPULATE_FROM_OLD_COL -> DROP_OLD_COL method.

No wonder i search docus 8.1.7 but can not find it,
but thanks for your infos about 9i.

Take care