DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Can you rename a column?

  1. #1
    Join Date
    Oct 2000
    Posts
    76
    Hi, This might be to obvious to some of you, but how, or if it is possible to rename an existing column with data already in it?

    Thanks.
    J.T.

  2. #2
    Join Date
    Jan 2000
    Posts
    21
    There is no command to drop columns until Oracle 8.1.

    The best way to do this in ver 7 - 8 is probably to rename the existing table. Then re-create the table looking the way you want followed by inserting the data using an insert as select statement.

    create table NEW_TABLE (column name datatype) AS SELECT * FROM THE OLD_TABLE.

    vidhya

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    The answer is yes and no.

    Yes means you can do in a round about manner like creating a view.

    create table test (n number);
    rename test to test123456789;

    create view test (m) as select n from test123456789;

    through view you can mask without really affecting the table or data's.

    otherwise you have drop and recreate.

    hope this helps
    Radhakrishnan.M

  4. #4
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    take a look at this example:
    create table klf (name varchar2(10), age number, sex varchar2(1));
    insert into klf values ('keith',38,'M');
    insert into klf values ('sue',39,'F');Suppose that we wanted to rename column 'n
    ame' to 'person'. The first step is to identify the object number for the table (as SYS)
    :
    select obj# from obj$
    where name='KLF'
    and owner#=9;(get the owner id from dba_users)

    OBJ#
    61341 Now identify the column numbers for the table:
    select obj#, col#, name from col$
    where obj#=61341;

    OBJ#COL#NAME
    613411NAME
    613412AGE
    613413SEX To change the name, a simple update statement will suffice:
    update col$ set name='PERSON'
    where obj#=61341
    and col#=1;
    To reflect the change in system views, you should now run catalog and catproc (un
    ix) eg:
    svrmgr> @$ORACLE_HOME/rdbms/admin/catalog
    svrmgr> @$ORACLE_HOME/rdbms/admin/catproc Now when you describe the table:
    desc klf
    NameNull ?Type
    -----------------------
    PERSON VARCHAR2(10)
    AGE NUMBER
    SEX VARCHAR2(1) Similarly, selecting

Posting Permissions

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


Click Here to Expand Forum to Full Width