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.
- Delete Columns Logically or make them unused columns
- Delete columns with data or physically deleting the columns
Table of Contents
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
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.