Oracle Drop Column

This tutorial walks you through various methods and techniques of dropping a table column in Oracle databases. Additionally, you will come across logical column drop and physical column drop in Oracle databases.

Sample Table

Before we dive into removing one or more columns from an oracle table, let us set up a sample data for illustration purposes.

Create a sample table in Oracle as shown in the following:

CREATE TABLE sample_data
(
id NUMBER,
first_name varchar2(50),
ip_address varchar2(20),
btc_address varchar2(50),
credit_card varchar2(50),
identifier varchar2(40),
CONSTRAINT sample_pk PRIMARY KEY (id)
);

Add the data to the table as follows:

INSERT INTO sample_data (id, first_name, ip_address, btc_address, credit_card, identifier)
VALUES (1, 'Wallas', '169.158.70.77', '1CNz5d1d5SC8SaR6dFSVihwztqYx5Fg77q', '4017955174552',
'26811d77-0a3a-4397-bc33-f7835f7c7ab9');
INSERT INTO sample_data(id, first_name, ip_address, btc_address, credit_card, identifier)
VALUES (2, 'Ian', '148.190.10.178', '1ADxBV7n9JeDDcb8pL24J9wV54mcSRHdu7', '4017956704480827',
'a69fe590-bc1b-4001-8ff8-154bcdb5802d');
INSERT INTO sample_data (id, first_name, ip_address, btc_address, credit_card, identifier)
VALUES (3, 'Pasquale', '150.86.18.140', '126hVKom2Foy9LEA6M4pUAT1h97c2rSD8B', '4017953296787867',
'34ac9385-9e1e-4d13-9537-c4eedb9f2c35');

SELECT * FROM users

Select the data from the table:

SELECT * FROM users

Table result:

Oracle Logical Column Delete

The first and most common method of removing a column from a given table is a logical delete. The specified column is disabled and cannot be accessed in a logical delete. Logical deletes are very useful, especially when working in a large table where removing a specific column can lead to performance deprivation.

The command syntax for logically removing a column is provided in the following:

ALTER TABLE TABLE_NAME
SET UNUSED COLUMN column_name;

To remove multiple columns, use the following syntax:

ALTER TABEL TABLE_NAME
SET UNUSED COLUMN (column_1, column_2, …column_N);

For example, to logically delete the ip_address column in the previous example table, we can run the following query:

ALTER TABLE sample_data
SET unused COLUMN ip_address;

The command requires you to have sufficient privileges on the target table.

Oracle Physical Column Delete

A physical deletion is the second method of removing a column from a table. This eliminates the column from the table.

The syntax is as provided in the following snippet:

ALTER TABLE TABLE_NAME DROP COLUMN column_name;

To delete the multiple columns, run the following command:

ALTER TABLE TABLE_NAME DROP (column_1, column_2, …columnN);

For example, to remove the ip_address and btc_address columns from the users’ table that we created earlier, we can execute the following statement:

ALTER TABLE sample_data DROP COLUMN (ip_address, btc_address);

NOTE: You can reduce the accumulated undo logs using the CHECKPOINT option, which enables a checkpoint after the defined number of columns is processed.

ALTER TABLE TABLE_NAME DROP unused COLUMNS checkpoint 250;

Conclusion

This post explored on how to use the Oracle alter command to delete a table column. However, removing a column may not be the most efficient method of freeing up a table space. Therefore, it is good to consider other options such as rebuilding or moving the table.

Oracle Drop Column

Sample Table

Oracle Logical Column Delete

Oracle Physical Column Delete

Conclusion

About the author

John Otieno