How
to drop a column of a table?
Question:
Is there a way to alter a
table and drop one column of the table?
Ans1:
You can:
create table as select (columns
you want) from old_table
Ans2:
Prior to 8i, you could use
a variety of tools to automate this (basically, each one copied the rows,
dropped the old table, created a new one minus the column, moved the data
back again, recreated necessary indexes, grants, synonyms etc.).
8i has a drop column syntax in the alter table that allows you to either
reclaim the space (i.e. actually delete the data for that column) or ignore
the column (thus allowing you to reclaim the space later in a maintenance
window)
Ans3:
Try the tool Hora from http://www.keeptool.com
. It generates and/or executes a script to drop or rename any column.
Ans4:
before Oracle8i, release 8.1 -- no, there is no command
to drop a column.
In Oracle8i, release 8.1 you can:
set a column to unused -- flag deletes a column, column
appears to be gone but
still consumes space on disk
drop a column -- column is physically removed
drop unused -- reclaims space from flag deleted columns.
Prior to that, you would either:
- drop and recreate the table. this is equivalent to dropping
the column as it
reclaims space.
- rename the table to something else and create a view
that selects out all but
the columns you wish to drop. this is the same
as setting unused.