Change a column name


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Change a column name

  1. #1
    Jawahar Rajan Guest

    Change a column name

    All,
    Is there a way to change a column Name of an existing table?
    Or will the table have to be dropped and then recreated.

    Thanks

    Jawahar



  2. #2
    Boris Milrud Guest

    Re: Change a column name


    Jawahar,

    Let's say you are SCOTT user, and you have table Test with 3 columns, i.e.

    Id integer not null,
    Name varchar2(30) ,
    Salary integer

    You want to rename column Name to a NewName, i.e.:

    Id integer not null,
    NewName varchar2(30) ,
    Salary integer

    Unfortunately, you can not rename column directly using one SQL command,
    i.e.:

    alter table Test
    rename ...


    Here are couple solutions for you, which will require to do multiple steps:


    Solution 1 - "Add/Drop":
    -----------------------

    If you are at Oracle 8i, you can drop column from the table. Using that new
    feature, you can just add new column to the table, move the data from the
    old column to a new column, and finally drop the new column.

    Step 1
    Drop all indexes and constraints that depend on Name column.

    Step 2
    Add NewName column to the table, i.e.:

    alter table Test
    add NewName varchar2(30);

    Step 3
    Move data from Name column to the NewName column:

    update Test
    set NewName = Name
    where Name is not null;

    commit;

    Step 4
    Now drop column Name:

    alter table Test
    drop (Name)
    cascade constraints;

    Step 5
    Recreate all indexes and constraints. Update statistics.

    Now table Test has following columns:

    Id integer not null,
    Salary integer ,
    NewName varchar2(30)


    Solution 2 - "Hack" (use at your own risk):
    ------------------------------------------

    You have to be a SYS user to implement this solution, because it will rename
    the column by updating Oracle dictionary directly.

    Again, if you are SYS user, then:

    Step 1
    Execute following query:

    select obj#,
    col#,
    name
    from col$
    where obj# = (select obj#
    from obj$
    where name = 'TEST'
    and owner# = (select user_id
    from dba_users
    where username = 'SCOTT'))


    The output of the above query is:

    OBJ# COL# NAME
    --------- ---------- ---------
    1337064 1 ID
    1337064 2 NAME
    1337064 3 SALARY

    where 1337064 is an Object# of your table Test.

    Step 2
    Update col$ Oracle dictionary:

    update col$
    set name = 'NEWNAME'
    where obj# = 1337064
    and col# = 2;

    commit;

    Step 3
    Update all Oracle dictionaries by running catalog.sql and catproc.sql scripts:

    @$ORACLE_HOME\rdbms\admin\catalog.sql
    @$ORACLE_HOME\rdbms\admin\catproc.sql

    Now table Test has following columns:

    Id integer not null,
    NewName varchar2(30) ,
    Salary integer

    Attention: this approach is not officially supported by Oracle.

    Hope it helps.

    Boris Milrud.





    Jawahar Rajan <Jawahar.Rajan@rtp.ppdi.com> wrote:
    >All,
    > Is there a way to change a column Name of an existing table?
    > Or will the table have to be dropped and then recreated.
    >
    >Thanks
    >
    >Jawahar
    >
    >



  3. #3
    woa tinh Guest

    Re: Change a column name


    Oracle 8i is suck!
    MS SQL Server can do it easily with 2 mouse clicks in the enterprise manager.

    "Boris Milrud" <milrud@hotmail.com> wrote:
    >
    >Jawahar,
    >
    >Let's say you are SCOTT user, and you have table Test with 3 columns, i.e.
    >
    >Id integer not null,
    >Name varchar2(30) ,
    >Salary integer
    >
    >You want to rename column Name to a NewName, i.e.:
    >
    >Id integer not null,
    >NewName varchar2(30) ,
    >Salary integer
    >
    >Unfortunately, you can not rename column directly using one SQL command,
    >i.e.:
    >
    >alter table Test
    >rename ...
    >
    >
    >Here are couple solutions for you, which will require to do multiple steps:
    >
    >
    >Solution 1 - "Add/Drop":
    >-----------------------
    >
    >If you are at Oracle 8i, you can drop column from the table. Using that

    new
    >feature, you can just add new column to the table, move the data from the
    >old column to a new column, and finally drop the new column.
    >
    >Step 1
    >Drop all indexes and constraints that depend on Name column.
    >
    >Step 2
    >Add NewName column to the table, i.e.:
    >
    >alter table Test
    >add NewName varchar2(30);
    >
    >Step 3
    >Move data from Name column to the NewName column:
    >
    >update Test
    >set NewName = Name
    >where Name is not null;
    >
    >commit;
    >
    >Step 4
    >Now drop column Name:
    >
    >alter table Test
    >drop (Name)
    >cascade constraints;
    >
    >Step 5
    >Recreate all indexes and constraints. Update statistics.
    >
    >Now table Test has following columns:
    >
    >Id integer not null,
    >Salary integer ,
    >NewName varchar2(30)
    >
    >
    >Solution 2 - "Hack" (use at your own risk):
    >------------------------------------------
    >
    >You have to be a SYS user to implement this solution, because it will rename
    >the column by updating Oracle dictionary directly.
    >
    >Again, if you are SYS user, then:
    >
    >Step 1
    >Execute following query:
    >
    >select obj#,
    > col#,
    > name
    >from col$
    >where obj# = (select obj#
    > from obj$
    > where name = 'TEST'
    > and owner# = (select user_id
    > from dba_users
    > where username = 'SCOTT'))
    >
    >
    >The output of the above query is:
    >
    > OBJ# COL# NAME
    >--------- ---------- ---------
    > 1337064 1 ID
    > 1337064 2 NAME
    > 1337064 3 SALARY
    >
    >where 1337064 is an Object# of your table Test.
    >
    >Step 2
    >Update col$ Oracle dictionary:
    >
    >update col$
    >set name = 'NEWNAME'
    >where obj# = 1337064
    >and col# = 2;
    >
    >commit;
    >
    >Step 3
    >Update all Oracle dictionaries by running catalog.sql and catproc.sql scripts:
    >
    >@$ORACLE_HOME\rdbms\admin\catalog.sql
    >@$ORACLE_HOME\rdbms\admin\catproc.sql
    >
    >Now table Test has following columns:
    >
    >Id integer not null,
    >NewName varchar2(30) ,
    >Salary integer
    >
    >Attention: this approach is not officially supported by Oracle.
    >
    >Hope it helps.
    >
    >Boris Milrud.
    >
    >
    >
    >
    >
    >Jawahar Rajan <Jawahar.Rajan@rtp.ppdi.com> wrote:
    >>All,
    >> Is there a way to change a column Name of an existing table?
    >> Or will the table have to be dropped and then recreated.
    >>
    >>Thanks
    >>
    >>Jawahar
    >>
    >>

    >



  4. #4
    Bob Guest

    Re: Change a column name


    "woa tinh" <woatinh@yahoo.com> wrote:
    >
    >Oracle 8i is suck!
    >MS SQL Server can do it easily with 2 mouse clicks in the enterprise manager.


    Sure - but how long does your SQL Server stay up? Three days? Four? And
    how many sites have had their SQL Server broken into?

    >
    >"Boris Milrud" <milrud@hotmail.com> wrote:
    >>
    >>Jawahar,
    >>
    >>Let's say you are SCOTT user, and you have table Test with 3 columns, i.e.
    >>
    >>Id integer not null,
    >>Name varchar2(30) ,
    >>Salary integer
    >>
    >>You want to rename column Name to a NewName, i.e.:
    >>
    >>Id integer not null,
    >>NewName varchar2(30) ,
    >>Salary integer
    >>
    >>Unfortunately, you can not rename column directly using one SQL command,
    >>i.e.:
    >>
    >>alter table Test
    >>rename ...
    >>
    >>
    >>Here are couple solutions for you, which will require to do multiple steps:
    >>
    >>
    >>Solution 1 - "Add/Drop":
    >>-----------------------
    >>
    >>If you are at Oracle 8i, you can drop column from the table. Using that

    >new
    >>feature, you can just add new column to the table, move the data from the
    >>old column to a new column, and finally drop the new column.
    >>
    >>Step 1
    >>Drop all indexes and constraints that depend on Name column.
    >>
    >>Step 2
    >>Add NewName column to the table, i.e.:
    >>
    >>alter table Test
    >>add NewName varchar2(30);
    >>
    >>Step 3
    >>Move data from Name column to the NewName column:
    >>
    >>update Test
    >>set NewName = Name
    >>where Name is not null;
    >>
    >>commit;
    >>
    >>Step 4
    >>Now drop column Name:
    >>
    >>alter table Test
    >>drop (Name)
    >>cascade constraints;
    >>
    >>Step 5
    >>Recreate all indexes and constraints. Update statistics.
    >>
    >>Now table Test has following columns:
    >>
    >>Id integer not null,
    >>Salary integer ,
    >>NewName varchar2(30)
    >>
    >>
    >>Solution 2 - "Hack" (use at your own risk):
    >>------------------------------------------
    >>
    >>You have to be a SYS user to implement this solution, because it will rename
    >>the column by updating Oracle dictionary directly.
    >>
    >>Again, if you are SYS user, then:
    >>
    >>Step 1
    >>Execute following query:
    >>
    >>select obj#,
    >> col#,
    >> name
    >>from col$
    >>where obj# = (select obj#
    >> from obj$
    >> where name = 'TEST'
    >> and owner# = (select user_id
    >> from dba_users
    >> where username = 'SCOTT'))
    >>
    >>
    >>The output of the above query is:
    >>
    >> OBJ# COL# NAME
    >>--------- ---------- ---------
    >> 1337064 1 ID
    >> 1337064 2 NAME
    >> 1337064 3 SALARY
    >>
    >>where 1337064 is an Object# of your table Test.
    >>
    >>Step 2
    >>Update col$ Oracle dictionary:
    >>
    >>update col$
    >>set name = 'NEWNAME'
    >>where obj# = 1337064
    >>and col# = 2;
    >>
    >>commit;
    >>
    >>Step 3
    >>Update all Oracle dictionaries by running catalog.sql and catproc.sql scripts:
    >>
    >>@$ORACLE_HOME\rdbms\admin\catalog.sql
    >>@$ORACLE_HOME\rdbms\admin\catproc.sql
    >>
    >>Now table Test has following columns:
    >>
    >>Id integer not null,
    >>NewName varchar2(30) ,
    >>Salary integer
    >>
    >>Attention: this approach is not officially supported by Oracle.
    >>
    >>Hope it helps.
    >>
    >>Boris Milrud.
    >>
    >>
    >>
    >>
    >>
    >>Jawahar Rajan <Jawahar.Rajan@rtp.ppdi.com> wrote:
    >>>All,
    >>> Is there a way to change a column Name of an existing table?
    >>> Or will the table have to be dropped and then recreated.
    >>>
    >>>Thanks
    >>>
    >>>Jawahar
    >>>
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center