Re: how to alter table columns:

From: Rauno Seppanen <rauno.seppanen_at_icon.fi>
Date: Wed, 11 Aug 1999 19:10:11 GMT
Message-ID: <nUjs3.268$yL6.809@read2.inet.fi>

>1) 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).
>
>
>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)
>
>
>
>2) for number 2 it is a simple matter of dropping the primary key
constraint
>(any release of Oracle)
>
>
>SQL> create table cmpd (
> 2 cmpd_formula varchar2(20) primary key,
> 3 ca_number varchar2(10));
>
>Table created.
>
>SQL> REM this gets the name of the primary key constraint on our table
>SQL> select constraint_name from user_constraints where table_name = 'CMPD'
> 2 and CONSTRAINT_TYPE = 'P'
> 3 /
>
>CONSTRAINT_NAME
>------------------------------
>SYS_C007528
>
>
>SQL> alter table cmpd drop constraint SYS_C007528;
>
>Table altered.
>

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

What do you think about updateing sys.col$ ? You need to flush (or shutdown and restart database) to see the change of the column. I did't once and it has worked fine. SQL> show user
user is "SYSTEM"
SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 EMPLOYEE_NAME                            VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SALARY                                   NUMBER(7,2)
 COMMISSION                               NUMBER(7,2)
 DEPTNO                                   NUMBER(2)
SQL> desc sys.obj$
 Name                            Null?    Type
 ------------------------------- -------- ----
 OBJ#                            NOT NULL NUMBER
 OWNER#                          NOT NULL NUMBER
 NAME                            NOT NULL VARCHAR2(30)
 NAMESPACE                       NOT NULL NUMBER
 TYPE                            NOT NULL NUMBER
 CTIME                           NOT NULL DATE
 MTIME                           NOT NULL DATE
 STIME                           NOT NULL DATE
 STATUS                          NOT NULL NUMBER
 REMOTEOWNER                              VARCHAR2(30)
 LINKNAME                                 VARCHAR2(128)
SQL> desc sys.col$
 Name                            Null?    Type
 ------------------------------- -------- ----
 OBJ#                            NOT NULL NUMBER
 COL#                            NOT NULL NUMBER
 SEGCOL#                         NOT NULL NUMBER
 SEGCOLLENGTH                    NOT NULL NUMBER
 OFFSET                          NOT NULL NUMBER
 NAME                            NOT NULL VARCHAR2(30)
 TYPE#                           NOT NULL NUMBER
 LENGTH                          NOT NULL NUMBER
 FIXEDSTORAGE                    NOT NULL NUMBER
 PRECISION                                NUMBER
 SCALE                                    NUMBER
 NULL$                           NOT NULL NUMBER
 DISTCNT                                  NUMBER
 LOWVAL                                   RAW(32)
 HIVAL                                    RAW(32)
 DEFLENGTH                                NUMBER
 DEFAULT$                                 LONG
 SPARE2                                   NUMBER
 SPARE3                                   NUMBER

UPDATE sys.col$
SET name = 'COMMISSION'
WHERE obj# = (SELECT obj# FROM sys.obj$

              WHERE name = 'EMP'
              AND owner# = 8)

AND name = 'COMM';

1 rows updated.
SQL> commit
  2 /
Commit complete.

Rauno Received on Wed Aug 11 1999 - 14:10:11 CDT