BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  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

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.