Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Question Unanswered: Change name to a table column

    Hi,
    I want to assign a new name to a table colum. I have already some data input into the database.

    I have access, as SYS user, to the Oracle data dictionary col$ and change the name.
    Checking the name on the table appears the new one, but when through the DBA Studio I try to access the data does not allow me. Thougth sqlplus the name is the old one.

    I have a form corresponding to that table, and the name seems to be the old one, so I gives an error when executing a Query or trying to save a new value.

    Any idea what´s should I do?

    Thanks,

    Maria Eugenia

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    RENAME COLUMN

    Hi

    In 9i, You can use:

    ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;




    Remi
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    1st off.... Never mess with the SYS tables ... you now have a descripency with other tables (user_tab_columns, etc ...)

    If you are using Oracle 8 ... you need to either 1. rebuild the table
    (possible build a new table and populate it with the data from the
    original table ... drop original table, rename new table)

    Add a new column to the existing table and populate it with the data
    from the original column, then drop the old column...

    Cleanest ... create new table, populate it with data from the original
    table, drop old table, rename new table ... Remember you have
    invalidated indexes and any other dependency objects (triggers, procs...)

    HTH
    Gregg

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Don't change the data dictionary row - the data dictionary cache is not changed if you do - so you may say goodbye to your database or instance ... depending on your being lucky or not (:-0

  5. #5
    Join Date
    Jun 2003
    Posts
    294

    Re- Create it

    If you are using oracle 9i, you can use ALTER TABLE.

    But if It's 8i or older, you must Re-Create the table.

    1) Make a copy of that table with the same structure (but the new name)
    2) Insert into table2 Select * from table1;
    3) drop the old table

  6. #6
    Join Date
    Apr 2004
    Posts
    1

    Rename column

    I am not sure in oracle but this stuff works in msde

    sp_rename('tablename.oldcolname','newcolname','COU LMN')

    sp_rename is a stored procedure.

    OR
    Add a new col name (Alter table tablename add col definition)

    update tablename set newcolname = oldcolname

    drop old col name ( Alter table tablename drop col)


    Bye

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •