DBA Tips Archive for Oracle

  


Dropping a column from a table - (Oracle 8i and higher)

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. Method 1: Marking a Column Unused / Physically Removing the Column
  3. Method 2: Physically Removing the Column
  4. Optional Syntax for Removing Columns

Overview

Prior to Oracle8i, there was never a clean way to DROP an unnecesasary column from a table. One of the methods was to dump the contents of the table to a flat file, drop the table, re-create the table without the column you want to drop, then use SQL*Loader, Pro*C, or PL/SQL to reload the table records to your newly created table. Depending on the size of the table, this process could take a long time, likely requiring a significant downtime to accomplish the task.

The syntax required to DROP a column from an existing table was introduced in Oracle8i. There are two methods used to DROP a column from an existing table. The first is a logical option that removes no data but otherwise behaves as if the column has been removed. This method is known as marking the column as unused. The second method physically removes the column from the table. The remaining sections of this article detail the concepts for each method in greater detail.

The examples below assume the table d_table is defined as follows:

  CREATE TABLE d_table (
      id_no     NUMBER
    , name      VARCHAR2(100)
    , d_column  VARCHAR2(100)
  )
  /

Method 1: Marking a Column Unused / Physically Removing the Column

An unused column cannot be seen by users of the table. Information about the unused column does not appear in the output of the describe table command, nor can any user query data in an unused column. Marking a column as unused is like deleting a column logically because the data is still in the table, but it cannot be seen or used. Use the following syntax to mark a column as unused:

  ALTER TABLE d_table SET UNUSED COLUMN d_column;

Users can query the dictionary view DBA_UNUSED_COL_TABS to see a list of all tables that have unused columns. The COUNT column in this view indicates how many unused columns there are for each table.

  SELECT * FROM sys.dba_unused_col_tabs;

If you wanted to now drop the unused column after marking it unused, you could use the following syntax:

  ALTER TABLE d_table DROP UNUSED COLUMNS;

Marking a column as unused and then using the alter table name drop unused column statement is useful because it allows the DBA to take away column access quickly and immediately. Later on, during a routine database maintenance weekend or after business hours, you can then remove the column with the alter table name drop unused column to reclaim the space.

Method 2: Physically Removing the Column

The other method for dropping an unnecessary table column is through the use of the the alter table name drop column colname statement. This statement actually removes all data from the column and eliminates the column from the table definition. This operation may take more time to complete than marking the column as unused, because Oracle has to go through all blocks of the table and actually remove the column data in order to reclaim the space used by that column. The following is an example of physically removing a table column:

  ALTER TABLE d_table DROP COLUMN d_column CASCADE CONSTRAINTS;

Optional Syntax for Removing Columns

There are a few optional clauses when using the alter table name drop column colname statement, which are all added on after colname in the statement:


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 14:32:27 EST
Page Count: 52486