The Oracle (tm) Users' Co-Operative FAQ

How do I drop a column ?


Author's name: Steve Bell

Author's Email: Stephen.Bell@sympatico.ca

Date written: 16 June 2001

Oracle version(s): 8.1.7.0

In the process of database development it may be desireable to change column definitions. Columns could always be added to a table, but prior to Oracle 8i, it was not possible to drop one or more columns from a table without recreating the table.

Back to index of questions


As of 8i the syntax exists to drop a column either immediately, or by marking it as "unused" to be dropped at a later time, presumably when the database is less busy.

To drop a column use the Alter Table command as follows. Note that you can't drop a pseudocolumn, a partition key column, or a column of a nested table.

To drop a column: alter table dept drop column loc; (to drop the loc column from the dept table). To mark a column as unused: alter table dept set unused column loc; (to mark the loc column in dept unused). To drop the unused columns and free up the space held by them: alter table dept drop unused columns; (to drop the unused column(s) in dept)

For information on unused columns you can query user_unused_col_tabs, dba_unused_col, or all_unused_col_tabs to see information on tables with columns marked as 'UNUSED'. Once you have marked a column as unused, you cannot access the column.

It is possible to drop more than one column at a time and it is possible to drop columns containing data. When dropping multiple columns omit the column keyword of the drop column syntax. Accordingly, to drop both the dname and loc columns from the dept table, use alter table dept drop(dname,loc);

Note in the syntax that the column keyword is omitted and the columns being dropped are in brackets. If the dropped columns are part of primary keys or unique constraints you must include the cascade constraints clause in the alter table command.

If you drop a column belonging to a primary key, Oracle will drop both the column and the associated primary key index.


Further reading:

Author

Title/URL

Suggested by

Referee's comments

Howard Rogers

Dropping columns from a table

Jonathan Lewis

One of several “discussion documents” on the Dizwell website that take the time and space to examine Oracle features properly.

 

 

 

 

 


Back to top

Back to index of questions