How to drop column using ALTER TABLE in Oracle?
Syntax
To drop a column in an existing table in Oracle, here is the syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
To drop multiple columns in an existing table:
ALTER TABLE table_name
DROP (column_name1, column_name2,…);
The DROP commands above are called physical delete. There’s another way to delete a column called logical delete, which sets the column as unused and you can delete it later.
To set a column as unused:
ALTER TABLE table_name
SET UNUSED COLUMN column_name;
After executing this command, the column will not be accessible anymore. So it won’t appear on data views or query results, all constraints and indexes for that column are also removed, but the column is still stored in the physical drive. This is particularly useful when you are in a rush and don’t want to wait for a long DROP query to finish.
To completely remove the column and free up the physical storage, run this command:
ALTER TABLE table_name
DROP UNUSED COLUMNS;
Example
When we don’t need the column salary from the table employees, execute the statement below to drop it:
ALTER TABLE employees
DROP COLUMN salary;
Or make it unused first:
ALTER TABLE employees
SET UNUSED COLUMN salary;
Then drop it when you have time:
ALTER TABLE table_name
DROP UNUSED COLUMNS;
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
