Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle Drop Column

Oracle Drop Column

Summary: in this tutorial, you will learn how to use the Oracle drop column statements to remove one or more columns from a table.

Oracle Drop Column using SET UNUSED COLUMN clause

The process of dropping a column from a big table can be time and resource consuming. Therefore, we typically drop the column logically by using the ALTER TABLE SET UNUSED COLUMN statement as follows:

ALTER TABLE table_name SET UNUSED COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)

Once you execute the statement, the column is no longer visible for accessing.

During the off-peak hours, you can drop the unused columns physically using the following statement:

ALTER TABLE table_name DROP UNUSED COLUMNS;
Code language: SQL (Structured Query Language) (sql)

If you want to reduce the amount of undo logs accumulated, you can use the CHECKPOINT option that forces a checkpoint after the specified number of rows has been processed.

ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
Code language: SQL (Structured Query Language) (sql)

Oracle SET UNUSED COLUMN example

Let’s create a table named suppliers for the demonstration:

CREATE TABLE suppliers ( supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY, contact_name VARCHAR2(255) NOT NULL, company_name VARCHAR2(255), phone VARCHAR2(100) NOT NULL, email VARCHAR2(255) NOT NULL, fax VARCHAR2(100) NOT NULL, PRIMARY KEY(supplier_id) );
Code language: SQL (Structured Query Language) (sql)

The following statements insert sample data into the suppliers table:

INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Solomon F. Zamora', 'Elit LLP', '1-245-616-6781', 'enim.condimentum@pellentesqueeget.org', '1-593-653-6421'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Haley Franco', 'Ante Vivamus Limited', '1-754-597-2827', 'Nunc@ac.com', '1-167-362-9592'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Gail X. Tyson', 'Vulputate Velit Eu Inc.', '1-331-448-8406', 'sem@gravidasit.edu', '1-886-556-8494'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Alec N. Strickland', 'In At Associates', '1-467-132-4527', 'Lorem@sedtortor.com', '1-735-818-0914'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Britanni Holt', 'Magna Cras Convallis Corp.', '1-842-554-5106', 'varius@seddictumeleifend.ca', '1-381-532-1632'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Audra O. Ingram', 'Commodo LLP', '1-934-490-5667', 'dictum.augue.malesuada@idmagnaet.net', '1-225-217-4699'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Cody K. Chapman', 'Tempor Arcu Inc.', '1-349-383-6623', 'non.arcu.Vivamus@rutrumnon.co.uk', '1-824-229-3521'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Tobias Merritt', 'Amet Risus Company', '1-457-675-2547', 'felis@ut.net', '1-404-101-9940'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Ryder G. Vega', 'Massa LLC', '1-655-465-4319', 'dui.nec@convalliserateget.co.uk', '1-282-381-9477'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Arthur Woods', 'Donec Elementum Lorem Foundation', '1-406-810-9583', 'eros.turpis.non@anteMaecenasmi.co.uk', '1-462-765-8157'); INSERT INTO suppliers (contact_name,company_name,phone,email,fax) VALUES ('Lael Snider', 'Ultricies Adipiscing Enim Corporation', '1-252-634-4780', 'natoque.penatibus@in.com', '1-986-508-6373');
Code language: SQL (Structured Query Language) (sql)

To logically drop the fax column from the suppliers table, you use the following statement:

ALTER TABLE suppliers SET UNUSED COLUMN fax;
Code language: SQL (Structured Query Language) (sql)

From now on, you cannot access the fax column anymore:

SELECT * FROM suppliers;
Code language: SQL (Structured Query Language) (sql)
Oracle DROP COLUMN example

You can view the number of unused columns per table from the DBA_UNUSED_COL_TABS view:

SELECT * FROM DBA_UNUSED_COL_TABS;
Code language: SQL (Structured Query Language) (sql)
Oracle DROP COLUMN - unused columns

As you can see, the suppliers table has one unused column.

To drop the all unused columns from the suppliers table, you use the following statement:

ALTER TABLE suppliers DROP UNUSED COLUMNS;
Code language: SQL (Structured Query Language) (sql)

Querying data from the DBA_UNUSED_COL_TABS view again, you will get an empty result set.

Oracle Drop Column using DROP COLUMN clause

To drop a column from a table physically, you use the following statement:

ALTER TABLE table_name DROP COLUMN column_name;
Code language: SQL (Structured Query Language) (sql)

To drop multiple columns, you use the statement below:

ALTER TABLE table_name DROP ( column_name_1, column_name_2 );
Code language: SQL (Structured Query Language) (sql)

Oracle DROP COLUMN clause example

The following statement removes the email and phone columns from the suppliers table:

ALTER TABLE suppliers DROP ( email, phone );
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle drop column statement to delete one or more columns from a table.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle ALTER TABLE MODIFY Column
Next Oracle DROP TABLE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.