Oracle Rename Column – alter table, rename column Example SQL Syntax

Posted on Jun 1, 2009 in Technical Tips | 5 comments



Following is simple example syntax for renaming a column to an Oracle table using “alter table” SQL:

1
2

ALTER TABLE [my_table_name]
RENAME COLUMN [current_column_name] TO [new_column_name];

A simple example would be:

1
2

ALTER TABLE employee
RENAME COLUMN f_name TO first_name;

Like renaming an Oracle table, you should be aware of any dependencies in code, etc. that reference a table column by name. Oracle does make the required changes within a database in many cases, but of course, you should ensure all is working and update any components that reside outside the database. You can use tools like TOAD to easily check dependencies on a table in a visual manner. Below is a screenshot within TOAD of the “Used By” tab that can be accessed when your table is selected in the schema browser:

used_by

Bookmark and Share

Related Information:

  1. alter table, add column – Oracle Example Syntax
    Following is simple example syntax...
  2. Oracle Rename Table – Example Syntax and Dependencies
    Renaming a table in Oracle...
  3. Copy Table Columns and Data
    As you may know, in...
  4. Oracle Materialized View DDL
    Are you a TOAD fanatic...
  5. Oracle TO_DATE Function – SQL Syntax Examples
    The Oracle TO_DATE SQL function...
  6. TOAD for Oracle 9 or TOAD 10 – 64-bit Windows – ORA-12154: could not resolve the connect identifier specified or ORA-6413: connection not open
    I recently downloaded the new...
  7. Create Table – ORA-00907: missing right parenthesis — CHECK and DEFAULT Order Swapped
    Most people, including myself, don’t...
  8. SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples
    The reserved word LIKE in...

5 Comments

  1. sharanabasava
    June 30, 2010

    ALTER TABLE [my_table_name]
    RENAME COLUMN [current_column_name] TO [new_column_name];

    Sir,I executed this command for altering the column name in my table but its giving the following error

    ERROR at line 1:
    ORA-14155: missing PARTITION or SUBPARTITION keyword

    reply
  2. Elavarasan
    February 17, 2011

    Really its good ..and helped me lot

    reply
  3. archana
    March 16, 2011

    i executed this query but it only works when there is no data in that column when data is inserted n this query is applied it gives error.so pls help mi to write query which can rename the column name or table name which contains data

    reply
    • ashok.aggarwal
      March 21, 2011

      I don’t think having data in the column is your issue since you can indeed rename a column even when data exists.

      Though this is not a support forum, feel free to share details of your environment and error message(s) in case someone has an idea for you.

      reply
  4. Polding
    May 24, 2011

    @archana,

    did you executed it as “SYS” user? syntax would be
    alter owner.table table_name rename column current_col_name TO new_col_name;
    hth.

    reply

Leave a Reply