Re: What is the way to rename or delete columns?

A copy of this was sent to "John" <hanjenq_at_ms21.hinet.net> (if that email address didn't require changing) On Thu, 12 Aug 1999 09:12:14 +0800, you wrote:

>Hi,
>
>My database server is Oracle 8.05 for NT.
>
>It seems there is no way to delete or rename columns directly, but what is
>the best way to rename or delete columns?
>
>Thanks!
>
>John
>
>

you cannot in any version of Oracle rename a column. In Oracle8i, release 8.1 you can drop a column so I suppose the steps of:

SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NUMBER                                     VARCHAR2(10)

SQL>
SQL> alter table cmpd add ca_no varchar2(10);

Table altered.

SQL> update cmpd set ca_no = ca_number;

(n) rows updated.

SQL> alter table cmpd drop column ca_number;

Table altered.

SQL>
SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NO                                         VARCHAR2(10)

would do it. I would suggest tho that a view is a more efficient way to do this (lots less io's on a big table). and it works with all releases.

SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NUMBER                                     VARCHAR2(10)

SQL>
SQL> rename cmpd to cmpd_table
  2 /

Table renamed.

SQL> create or replace view cmpd ( cmpd_formula, ca_no )   2 as
  3 select cmpd_formula, ca_number from cmpd_table   4 /

View created.

SQL>
SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NO                                         VARCHAR2(10)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Aug 12 1999 - 06:22:45 CDT