Re: How to drop a column?

From: Spencer <spencerp_at_swbell.net>
Date: Fri, 29 Jun 2001 21:56:03 -0500
Message-ID: <2db%6.118$uk1.7685@nnrp1.sbc.net>

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9hi79602b8o_at_drn.newsguy.com...
> In article <9hi4k1$cum$1_at_stargate1.inet.it>, "Alessio" says...
> >
> >Hi to all, i'm a newbie!
> >Which is the sintax for dropping a column of a table?
> >It is possible to drop a col?
> >thanks,Alessio
> >
> >
>
> why does no one use a version....
>

you do you mean, Thomas ? lol.... you mean everyone isn't already running Oracle9j Release 7 (9.2.11.0.0) on RedHat 8.8k ? i mean, apart from those that are trying to install Oracle 7.0 to Windows 2112 running on Itanium.

> in Oracle8i, you can drop or set unused a column (drop rewrites, unused
 "flag"
> deletes the column)
>

as you point out, Thomias, in earlier versions, you'd need create a new table without the column, and then insert all of the rows from the existing table (leaving out the column you want to drop, of course), and then rename the tables.

of course, you need to modify and/or re-compile all of the dependencies (views, synonyms, pl/sql objects), nd grant privileges, etc.

>
>
> tkyte_at_TKYTE816> desc emp
> Name Null? Type
> ----------------------------------------------------- -------- -----------

---

>
> EMPNO NOT NULL NUMBER(4)
> ENAME

 VARCHAR2(10)
> JOB

 VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL

 NUMBER(7,2)
> COMM

 NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> tkyte_at_TKYTE816> alter table emp drop column deptno;
>
> Table altered.
>
>
> tkyte_at_TKYTE816> alter table emp set unused (comm);
>
> Table altered.
>
> tkyte_at_TKYTE816> desc emp
> Name Null? Type
> ----------------------------------------------------- -------- -----------

---

>
> EMPNO NOT NULL NUMBER(4)
> ENAME

 VARCHAR2(10)
> JOB

 VARCHAR2(9)
> MGR NUMBER(4)
> HIREDATE DATE
> SAL

 NUMBER(7,2)
>
>
>
> In 8.0 and before, you would rename the table and create a view that is
 named as
> the table was that does not select out that column (sort of like setting
 UNUSED)
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
>
Received on Fri Jun 29 2001 - 21:56:03 CDT