Drop Columns Or Delete Columns In Oracle Table Query

To Delete columns in Oracle, we have to use ALTER TABLE…DROP COLUMN statement query.

Dropping columns in oracle can be done in 2 ways.

1. Delete Columns Logically or make them unused columns
2. Delete columns with data or physically deleting the columns

On this page

Delete columns in oracle table logically: #

Logically deleting columns in oracle is nothing but setting unused columns.

Once you mark the column as unused you can longer see that column in oracle table.

Dropping a column from a huge table is time consuming & uses too many resources.So it’s better to mark column as unused and can be deleted after some time.

• Once the column marked as unused it will be no longer visible in oracle select queries and data dictionary views.
• All indexes, statistics, constraints created on that columns are also removed.
• But it does not actually remove the target column data or restore the disk space occupied by these columns.
• We can also reuse the column name to create new columns.

Delete a single column in oracle table Logically: #

To delete a single column in oracle table logically use below query

ALTER TABLE oracle_table_name SET UNUSED (column_to_be_deleted);

We have to pass column name to the unused statement.

For example if you want to mark department_id from employee table as unused use the below query

ALTER TABLE employee SET UNUSED (department_id);

Delete multiple columns in oracle table Logically: #

To delete multiple columns in oracle table logically use the following UNUSED statement query

ALTER TABLE oracle_table_name SET UNUSED (column_to_be_deleted1,column_to_be_deleted1);

We have to pass multiple column names to unused statements as shown above.

For example, the below oracle query marks department_id and is_manager columns in the employee table as unused.

ALTER TABLE employee SET UNUSED (department_id,is_manager);

Deleting unused columns in oracle table: #

To permanently delete the unused columns in oracle use the below DROP UNUSED query statement

ALTER TABLE oracle_table_name DROP UNUSED COLUMNS;

For example, the below oracle query permanently deletes department_id,is_manager columns from the employee table, which are marked as unused

ALTER TABLE employee DROP UNUSED COLUMNS;

We can specify the checkpoint clause to avoid the amount of undo logs created during the drop column query to avoid potential exhaustion of undo space.

The below query creates a checkpoint for every 300 records processed.

ALTER TABLE employee  DROP UNUSED COLUMNS CHECKPOINT 300;

View all unused columns in Oracle Database query: #

To view all unused columns in oracle database we use the following data dictionary views

1. USER_UNUSED_COL_TABS
2. ALL_UNUSED_COL_TABS
3. DBA_UNUSED_COL_TABS

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                   TABLE_NAME            COUNT
---------------------   ---------------       -----
DBA                     EMPLOYEE              2

The count column displays the number of unused columns in a given table.

Delete column with data in oracle or Physically Deleting: #

We can use ALTER TABLE…DROP COLUMN statement to delete the column with data from oracle table.

Delete a single column in oracle table query: #

To delete a single column in oracle table use the following query

The DROP COLUMN statements delete columns including their data.

ALTER TABLE oracle_table_name DROP COLUMN column_to_be_deleted;

The following query deletes the department_id column from the employee table

ALTER TABLE employee DROP COLUMN department_id;

Delete multiple columns in oracle table query: #

To drop multiple columns in oracle table use the below query statement.

ALTER TABLE oracle_table_name DROP COLUMN (column_to_be_deleted1,column_to_be_deleted2);

The following query deletes the department_id,is_manager columns from the employee table.

ALTER TABLE employee DROP COLUMN (department_id,is_manager);

Deleting columns from oracle compressed tables: #

If we enable compression for all operations in the oracle table, you can drop table columns as shown above.

But If you enable compression only for direct-path inserts, you cannot drop columns from the table.