Skip to Main Content
  • Questions

  • Dropping a column in a table (Version Before 8.1.x)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, balasubramanian.

Asked: September 27, 2000 - 2:08 am UTC

Last updated: June 05, 2003 - 9:23 am UTC

Version: 8.0.6/8.1.5

Viewed 1000+ times

You Asked

Hi Tom, In oracle 8i we can drop a column. But I want to drop a column in a table in oracle version 8.0.6. To do that,can I proceed in the following ways:

1.I will get the object_id of the table by sql:
select object_id from dba_objects where object_name ='X';
2.I will delete the second column of the above object by sql:
Delete from col$ where obj#=above_id and col#=2;
Update tab$ set cols = (old_cols - 1);
Commit;

3.Shutdown and restart the oracle instance.

Is it illegal to do like that? I think, Oracle Corp. will not be responsible for any future crashes of my database, is'nt it?



and Tom said...

That is the fastest way to terminate your support, yes.

Do not in any circumstances do that. It'll totally mess up your database.

The method to "drop a column" in 8.0 and before would be:

o rename X to X_OLD;
o create view X as select <all but column to drop> from X_OLD;
o revoke everything from X_OLD
o grant everything you need on X
o alter X_OLD modify column_to_be_dropped NULL; (if it is
currently NOT NULL)

The column will appear to be gone as far as anyone is concerned.

You can of course do this physically by moving lots of bytes around (create table as select, drop old table, recreate it, reload it) but the view works great.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What About Update

Hatem, June 05, 2003 - 3:24 am UTC

Dear Tom
your solution of creating view on the old table will be usefull only if am going to select
but in case of updating or insert?
I want to drop columns from tables on oracle db version 8.0.5 or mark it as unused (as in 8i) i'm afraid my application may refer to.

Tom Kyte
June 05, 2003 - 7:59 am UTC

views work just dandy for this. what problem do you forsee with DML on a view that simply hides colums?

Drop columns in versions prior 8i

Hatem, June 05, 2003 - 8:16 am UTC

Dear Tom
my problem is that my application refers to columns i no longer user, i've readjust my application to no longer user such columns and i want be 100% certain that these columns no longer used.
i've tried to recreate tables and reload it but it will take too much time as many columns i need to drop it.
any hints?

Tom Kyte
June 05, 2003 - 8:41 am UTC

rename the table T to T_TABLE;

create view T as select <columns you want> from T_TABLE;


columns are "dropped". Now, if your application still references them - I really don't know what you want from me. You obviously cannot "drop" them can you? they are still used.

drop columns in 8.0.5

hatem, June 05, 2003 - 9:06 am UTC

what about updating and inserting data in this view?
how to update and insert in views????
my problem is not adjusting my application my problem is how to drop such columns or mark them unused?

Tom Kyte
June 05, 2003 - 9:23 am UTC 

just do it.

Ok, we have the EMP table, we want to "drop" HIREDATE:

ops$tkyte@ORA806> create table emp as select * from scott.emp;

Table created.

ops$tkyte@ORA806>
ops$tkyte@ORA806> 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)

ops$tkyte@ORA806> rename emp to emp_table;

Table renamed.

ops$tkyte@ORA806> create or replace view emp
  2  as
  3  select empno, ename, job, mgr, sal, comm, deptno
  4    from emp_table
  5  /

View created.

ops$tkyte@ORA806> desc emp;
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

ops$tkyte@ORA806>
ops$tkyte@ORA806> insert into emp
  2  ( empno, ename, job, mgr, sal, comm, deptno )
  3  values
  4  ( 1234, 'blah', 'x', null, 100, 200, 10 );

1 row created.

ops$tkyte@ORA806>
ops$tkyte@ORA806> update emp
  2  set ename = 'halb' where empno = 1234;

1 row updated.

ops$tkyte@ORA806>
ops$tkyte@ORA806> delete from emp where empno = 1234;

1 row deleted.



<b>so, I ask you again -- what is the problem you anticipate here -- I've told you exactly what you need to do.  Just do it?  Column "gone"</b>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.