alter table drop column in oracle database

We often need to drop the column in the table. There are two ways to drop the column in Oracle
(a) alter table drop column in oracle
(b) alter table set unused column in oracle

DROP COLUMN using DROP COLUMN

Here in this we drop the column from table using below command.

ALTER TABLE table_name
DROP COLUMN col_name;

This statement takes exclusive lock on the table and if the table is big, it will take time to execute the query

Example

SQL> CREATE TABLE Books_master
( Book_Id NUMBER(6,0),
Title VARCHAR2(50),
Author VARCHAR2(50),
ISBN VARCHAR2(25),
BCost NUMBER(6,0),
Publish_Year NUMBER(4,0),
CONSTRAINT Books_master_PK PRIMARY KEY (Book_Id),
CONSTRAINT Books_master_UK1 UNIQUE (Title, Author),
CONSTRAINT Books_master_UK2 UNIQUE (ISBN)
) ;
Table created.

SQL> INSERT INTO Books_master (Book_Id, Title, Author, ISBN, BCost, Publish_Year) VALUES (10, 'Complete/Convenient', 'Ketan Bhagat', 'ISBN 978-93-80349-92-3', 195, 2013);
SQL>INSERT INTO Books_master (Book_Id, Title, Author, ISBN, BCost, Publish_Year) VALUES (20, 'Deception Point', 'Dan Brown', 'ISBN 0-671-02738-7', 563,2001);
SQL> INSERT INTO Books_master (Book_Id, Title, Author, ISBN, BCost, Publish_Year) VALUES (30, 'Angels-Demons', 'Dan Brown', 'ISBN 0-671-02736-0',563, 2000);
SQL>INSERT INTO Books_master (Book_Id, Title, Author, ISBN, BCost, Publish_Year) VALUES (40, 'Harry Potter', 'J.K. Rowling', 'ISBN 0-7475-5100-6', 102,2003);

SQL> commit;
Commit complete.

SQL> column TITLE format a10
SQL> column AUTHOR format a10
SQL> column ISBN format a15

SQL > Desc BOOKS_MASTER

Lets drop a column from this table

SQL> alter table books_master drop column PUBLISH_YEAR;
Table altered.

SQL > Desc BOOKS_MASTER

SQL> Select * from Books_master;

DROP COLUMN using SET UNUSED

• if you are concerned about the resource consumed in dropping the column, you can use the ALTER TABLE…SET UNUSED statement.
• This statement marks one or more columns as unused but does not actually remove the target column data or restore the disk space occupied by these columns.
• A column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

Syntax

ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;

We can drop the unused column later on when the resource is lower using the command

ALTER TABLE <table_name>
Drop  UNUSED COLUMNS ;

If you have large tables, you can reduce the amount of undo logs accumulated using the CHECKPOINT option, which forces a checkpoint after the specified number of rows have been processed. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid potential exhaustion of undo space.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 500;

• If you have used the checkpoint syntax with your drop command then you could kill the session (or shut down the database) halfway through the drop.
• The table will not be available for DML or query
• you can finish off the “drop columns” command using the “continue” (with an optional checkpoint) option.

alter table
drop columns continue checkpoint;

alter table
drop columns continue checkpoint 5000;

Example
lets go with the previously created table

SQL> alter table books_master set unused column ISBN;
Table altered.
SQL> desc books_master

SQL> Select * from Books_master;

We can check the unused column count by querying the below view USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS

SQL> select * from DBA_UNUSED_COL_TABS where TABLE_NAME='BOOKS_MASTER';

Now you can drop the column during low activity period

SQL> alter table Books_master drop unused columns;
Table altered.

SQL> select * from DBA_UNUSED_COL_TABS where TABLE_NAME='BOOKS_MASTER';
no rows selected

How to drop Multiple column

We can specify multiple column during drop command

SQL> alter table books_master drop (AUTHOR,TITLE);
Table altered.

Hope you like post on alter table drop column in oracle. Please do provide the feedback

Also Reads
Oracle primary key
alter table move
alter table rename column in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables006.htm