RENAME COLUMN WHERE NO DATA 2

[mkey]

Hi All,
I tried this script to rename a column that is empty.

UPDATE SYS.COL$ C
SET C.NAME = 'CANDIDATE_BALANCE'
WHERE
C.NAME = 'BALANCE' AND
C.OBJ# IN (SELECT O.OBJ#
FROM SYS.OBJ$ O, SYS.USER$ U
WHERE
O.OWNER# = U.USER#
AND
U.NAME = 'SCOTT'
AND O.NAME = 'ACCOUNT_ONE');
COMMIT;

When I ran this script and the feedback was 1 row updated. But when I did a desc on the account_one I do not see the column name change. Can someone tell me what I did wrong here?
Thanks
mkey

 

[jee]

I don't believe you can rename columns. I think what you would need to do is drop the old column (since the column is empty) and add a new one with the new name - you do both with the ALTER TABLE sql statement.

Note: you don't make database structure changes by updating system tables/views - those are really meant to be treated as read-only.

 

[THoey]

I am still trying to think of a time where I would EVER want to rename a column in code. It would seem that not enough time was spent in the design mode, if you are in a position to have to rename columns and fields.

Please don't take that as a put down, you may have a perfectly logical reason to do this. I just have never seen a good reason for it... Terry M. Hoey

 

[mkey]

Hi Terry,
I just want to make sure that I didn't change anything in the system files. I just think there should be a way to rename a column in oracle thats all. Don't break your head for this. Thank you for all your support.
mkey

 

[karluk]

Yikes, I think you have to assume that your update statement modified the system catalog. That "one row updated" message seems pretty clear to me.

If you can, I suggest that you drop and recreate the table. Most likely that will fix the catalog.

In general, never update catalog tables directly, unless specifically instructed to do so by Oracle support. The minimal benefits of doing so are vastly outweighed by the dangers.

 

[carp]

While it would be nice if there was a simple way to rename columns in Oracle, you have to keep in mind that we didn't get the capability to drop columns until 8i; renaming them may be years out!

To echo Karluk's point - you'll be much happier if you NEVER try to directly mess around in your data dictionary (unless, again, you have Oracle Support's guidance). You're much better off altering your objects and letting the system take care of the DD.

 

[liyakat]

Hi this is liyakat
do the follow the text below and ur work will be done

Renaming columns is a time-honored problem when it comes to Oracle... Once a table is created, there is no way to change the name of a column, other than to drop the table and re-create it with the changed column name. That is, there is no official way to do it.... An unofficial way is to manipulate one of the underlying fixed tables.
CAUTION: It is strongly suggested that this not take place in a production system environment. This should be accomplished in a separate test or development database..
Starting with an existing table:

create table fuzzy_slippers
(slipper# number,
type varchar2(10),
color varchar2(10));

insert into fuzzy_slippers values (12,'Child','BLUE');
insert into fuzzy_slippers values (34,'Mens','BROWN');
Suppose that th column name 'slipper#' should be 'style#'.
The first step is to identify the object number for the table (as SYS):

select obj# from obj$
where name='FUZZY_SLIPPERS' and owner#=9;

NOTE: Get the owner id from dba_users

OBJ#
58217

Now identify the column numbers for the table:
select obj#, col#, name from col$ where obj#=58217;

OBJ# COL# NAME
58217 1 SLIPPER#
58217 2 TYPE
58217 3 COLOR

To change the name, a simple update statement will suffice:

update col$ set name='STYLE#'
where obj#=58217 and col#=1;

To reflect the change in system views, run catalog and
catproc (unix) eg:

svrmgr> @$ORACLE_HOME/rdbms/admin/catalog
svrmgr> @$ORACLE_HOME/rdbms/admin/catproc

Now when a describe of the table is done:
desc fuzzy_slippers

Name Null ? Type
STYLE# NUMBER
TYPE VARCHAR2(10)
COLOR VARCHAR2(10)

Similarly, selecting from the table and querying views such as

DBA_TAB_COLUMNS shows the new column name.
NOTE: Before changing column names, drop any indexes, foreign keys and primary constraints that might be on the column. These can be re-created later.


Bye

Liyakat