 |
|
Oracle Tips by Burleson |
Altering Tables: Drop Columns
Sometimes, you find that you have columns that are not being used.
The data no longer is collected or relevant. In this case, you need
to remove some columns from your database. With Oracle8i and later,
you can remove a column from a table with the ALTER TABLE DROP COLUMN
command.
SQL> desc
author
Name Null? Type
----------------------------------- -------- ----------- AUTHOR_KEY VARCHAR2(11)
AUTHOR_LAST_NAME VARCHAR2(40)
AUTHOR_FIRST_NAME VARCHAR2(20)
AUTHOR_PHONE VARCHAR2(12)
AUTHOR_STREET VARCHAR2(40)
AUTHOR_CITY VARCHAR2(20)
AUTHOR_STATE VARCHAR2(2)
AUTHOR_ZIP VARCHAR2(5)
AUTHOR_CONTRACT_NBR NUMBER(5)
SQL> alter
table author drop column author_contract_nbr;
Table
altered.
SQL> desc
author
Name Null? Type
------------------------------------- -------- ---------- AUTHOR_KEY VARCHAR2(11)
AUTHOR_LAST_NAME VARCHAR2(40)
AUTHOR_FIRST_NAME VARCHAR2(20)
AUTHOR_PHONE VARCHAR2(12)
AUTHOR_STREET VARCHAR2(40)
AUTHOR_CITY VARCHAR2(20)
AUTHOR_STATE VARCHAR2(2)
AUTHOR_ZIP VARCHAR2(5)
If the column you dropped had an index or a
constraint defined on it, those objects would also be dropped. If the
column is part of a multicolumn constraint, you must use “cascade
constraint” in the ALTER TABLE command and the entire constraint is
also dropped.
SQL> alter
table author
2 drop column author_contract_nbr cascade constraint;
Table
altered.
Dropping a column can be a time and resource
consuming operation if the table is large. As an alternative to
dropping a column, you can set the column as unused. The column
actually stays in the table, but the database does not allow access to
it. Later, when you have a maintenance period, you can drop the
unused columns.
SQL> desc
author
Name Null? Type
------------------------------------- -------- ---------- AUTHOR_KEY VARCHAR2(11)
AUTHOR_LAST_NAME VARCHAR2(40)
AUTHOR_FIRST_NAME VARCHAR2(20)
AUTHOR_PHONE VARCHAR2(12)
AUTHOR_STREET VARCHAR2(40)
AUTHOR_CITY VARCHAR2(20)
AUTHOR_STATE VARCHAR2(2)
AUTHOR_ZIP VARCHAR2(5)
AUTHOR_CONTRACT_NBR NUMBER(5)
SQL> alter
table author set unused column author_first_name;
Table
altered.
SQL> desc
author
Name Null? Type
------------------------------------- -------- ---------- AUTHOR_KEY VARCHAR2(11)
AUTHOR_LAST_NAME VARCHAR2(40)
AUTHOR_PHONE VARCHAR2(12)
AUTHOR_STREET VARCHAR2(40)
AUTHOR_CITY VARCHAR2(20)
AUTHOR_STATE VARCHAR2(2)
AUTHOR_ZIP VARCHAR2(5)
AUTHOR_CONTRACT_NBR NUMBER(5)
SQL> select *
from user_unused_col_tabs;
TABLE_NAME
COUNT
------------------------------
----------AUTHOR 1
1 row
selected.
SQL> alter
table author drop unused columns;
Table
altered.
SQL> select *
from user_unused_col_tabs;
no rows
selected
In the example above, I marked the
author_contract_nbr column as
unused. Once marked unused, the command cannot be rolled back. The
column is gone for all practical purposes. The cleanup is delayed
until the DROP UNUSED COLUMNS command is
issued. There is a view, called the
user_unused_col_tabs view that contains the
table name and the number of columns marked unused. In the example
above it, the AUTHOR table contained one unused column. Once I
dropped the unused columns, the view was empty.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
