Re: Renaming a field

Hm, it sounds pretty easy to do it this way -- but it will only work on a stand-alone table without
any index constraint and so on.
Before you drop the table you should better have a look at the system-tables given below. They all contain additional information on the table/column, which will be implicitly dropped, if you drop your_table. The better way:

• read out and save additional information on your table (worst case on paper)
• remove references on your_table
• rename your column (may be in the given way)
• restore the additional information's (references on your_table last)

Check these tables (hope i got the most imported): USER_CONSTRAINTS on your_table

    select *
    from user_constraints
    where table_name = 'your_table'

USER_CONSTRAINTS referencing your_table

    select ref.*
    from user_constraints prim, user_constraints ref     where ref.constraint_type = 'R'
    and ref.r_owner = prim.owner
    and ref.r_constraint_name = prim.constraint_name     and prim.table_name = 'your_table'

USER_CONS_COLUMNS on your_table

    select *
    from user_cons_columns
    where table_name = 'your_table'

USER_INDEXES on your table

    select *
    from user_indexes
    where table_name = 'your_table'

USER_TRIGGER on your table

    select *
    from user_trigger
    where table_name = 'your_table';

USER_COL_COMMENTS on your table

    select *
    from user_col_comments
   where table_name = 'your_table';

USER_TAB_COMMENTS on your table

    select *
    from user_tab_comments
   where table_name = 'your_table';

USER_COL_PRIVS on your table

    select *
    from user_col_privs
   where table_name = 'your_table';

USER_TAB_PRIVS on your table

    select *
    from user_tab_privs
   where table_name = 'your_table';

hope it would help...

Tanasescu iosif wrote:

> Try :
> 1) first create a view with the new name for all fields you want, for all
> others put the old name.
> ( lets say :
> create view temp_view ("new_col_name1", "old_col2", ...)
> as select * from your_table;
> )
> 2) create a temporary table with
> create table temp_table as select * from temp_view ;
> 3) now yuo can drop the view and the originaly table
> drop view temp_view;
> drop view your_table;
> 4)a) recreate the table ( if you are with ORACLE below 8.0 )
> create table your_table as select * from temp_table;
> b) or rename the table ( if you are with ORACLE 8.0 and above ).
> alter table temp_table rename to your_table;
>
> Regards
> Iosif
> DBA - MEI Mtl.
 

> James Wj Snyder <snyderj_at_wellsfargo.com> wrote in article
> <01bce65c$d4205640$5a4e9797_at_snyderj.wellsfargo.com>...
> > Hello Peoples,
> >
> > anyone know of a way to rename an oracle field. With ANY tool?
> > --
> > Peace,
> > James Wj Snyder
> > Shattered Rose Studio
> > ** ClubWin Member **
> > www.shatteredrose.com
> >
Received on Sun Nov 02 1997 - 00:00:00 CET