Re: How to change a field name?

Heggelund wrote:
>
> Hi,
>
> there is a quick way of doing this but it is not described in any manual
> nor is it supported by Oracle. But if you really need to change the column
> name you could do this.
>
> Connect as internal or sys.
>
> Find the tables obejct_number: select obj# from obj$ where name = 'table_name';
> Then change the name in col$:
> update col$ set name = 'new_name'
> where obj# = 'object_number' -- retrieved previously
> and name = 'old_name'
> /
>
> alter system flush shared_pool; -- To get rid of the old dd information in
> the rowcache.
>
> I have just done this a couple of times, and never in a production environment.
> I don't recommend updating the sys tables, but as far as I have seen it
> should work.

If you do this, you're taking your life in your own hands. Hey, it might work great, but if I found out that someone was modifying the data dictionary on one of my databases like this, especially a production DB, I'd have their head on a platter. When you mess with stuff that you're not supposed to you cannot be certain that you won't inadvertantly corrupt your database. This is just plain LAZY. If you really need to do it, do it the right way. If you permanently need to change a column name, use the CREATE TABLE AS (subquery)... method then drop the original table, making certain any constraints are carried over. The view method may work as well depending on why you need to change the column name. But not only do I not "recommend" changing the data dictionary, but if I were your boss and I found out that you had, I'd probably fire you. (whew! got a little peeved there didn't I? sorry)

• TRW