Re: Renaming Columns

From: Hakan <nvcinc_at_ibm.net**no_spam_please**>
Date: Thu, 27 Aug 1998 00:46:43 -0400
Message-ID: <6s2okb$k6g$1@news.interlog.com>

Hi,

I did it several times. It works. An example is as follows:

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

SQL> UPDATE sys.col$
  2 SET name = 'COMMISSION'

  3      WHERE obj# = (SELECT obj# FROM sys.obj$
  4                      WHERE name = 'EMP'
  5                        AND owner# = 8)
  6        AND name = 'COMM'

  7 /

1 rows updated.

SQL> commit
  2 /

Commit complete.

SQL> select u.name, o.name,
  2         c.name
  3  from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u
  4  where o.obj# = c.obj#
  5    and o.owner# = u.user#
  6    and c.obj# = h.obj#(+) and c.col# = h.col#(+)

  7 and o.type in (2, 3, 4)
  8 and o.name = 'EMP'
  9 /

NAME                           NAME                           NAME
------------------------------ ------------------------------
------------------------------
SCOTT                          EMP                            EMPNO
SCOTT                          EMP                            EMPLOYEE_NAME
SCOTT                          EMP                            JOB
SCOTT                          EMP                            MGR
SCOTT                          EMP                            HIREDATE
SCOTT                          EMP                            SALARY
SCOTT                          EMP                            COMMISSION
SCOTT                          EMP                            DEPTNO

8 rows selected.

I changed ename to employee_name as well.

However desc <table-name> sometimes does not work. You have to flush SGA or shutdown/start-up database.

One more thing, this is not recommended at all times. But it is good to know.

Hakan Eren
NVC Inc.

mtillberg_at_my-dejanews.com wrote:

> I know it's been posted that columns cannot be renamed, but I was wondering
> if anyone has tried this by directly modifing the sys.col$ table. I'd do it
> myself, but I'm more of a programmer than a DBA and we don't have a very good
> backup system going yet. I've done something similiar to this, modifying the
> on delete cascade flag for constraints. I update the refact column in the
> sys.cdef$ table. It works, although it seems to take a while for the changes
> to take effect. I was wondering if this would also work for the column
> names.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Wed Aug 26 1998 - 23:46:43 CDT