Re: How to change a field name?

Hi,

The solution I described was ment as a real crisis solution the same way as using underscore parameters or setting events in init.ora would be a real crisis solution. These teqniques must not be used as a normal way of life excactly because you don't know all the implications. But if you are willing to take the risk, and this is not a production database, and the amount of work changing the column name the right way would take days, then you could try this. But be sure to have a good backup of you database before you start.

I would also fire anybody messing with ny databases like this without taking the necessary precautions.

Rgds
Steinar Heggelund

Medic Alert (news_at_medicalert.org) wrote:
: 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

--

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.