Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Altering Tables: Add Columns

Once we have our tables, we may need to make some changes as our database grows and our data changes.  If the table is empty, we normally just drop and recreate it.  If the table has data, then we need to modify the table in place with the ALTER TABLE command.  Note that the ALTER TABLE command is DML and issues an implicit commit. 

Sometimes, we find that a piece of data that we did not maintain becomes important, and we need to add it to the database.  We can add a table to hold the new data or add it to our current schema by adding a column to a current table.  For example, we discover that we need to keep a record of the last date that each author published and what they published.  We need to add two columns to the author table, author_last_published (a date) and author_item_published (a varchar2(40)).  To do this, we use the ALTER TABLE ADD command. 

SQL> alter table author add (author_last_published  date); 

Table altered. 

SQL> alter table author add (author_item_published  varchar2(40));

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)
 AUTHOR_CONTRACT_NBR                            NUMBER(5)
 AUTHOR_LAST_PUBLISHED                          DATE
 AUTHOR_ITEM_PUBLISHED                          VARCHAR2(40)
 

Notice that the new columns are at the end of the AUTHOR table.  All current rows in the table now contain NULLs for the new columns. 

select
  author_key,
  author_last_published,
  author_item_published
from
  author; 

AUTHOR_KEY  AUTHOR_LA AUTHOR_ITEM_PUBLISHED
----------- --------- ----------------------------------------
A101
A102
A103
A104
A105
A106
A107
A108
A109
A110

10 rows selected. 

I added each column separately, but you can add as many columns as needed in one command by separating them with commas.

alter table author add (author_last_published  date,
                        author_item_published  varchar2(40));

If I define a default value for the new columns, all the current columns will have the default value.  (I dropped and recreated the original author table.)

SQL> alter table author add (
  2     author_last_published  date default SYSDATE,
  3     author_item_published  varchar2(40)
  4                 default 'Magizine Article' not null
  5  ); 

Table altered. 

select
  author_key,
  author_last_published,
  author_item_published
from
  author; 

SQL>   

AUTHOR_KEY  AUTHOR_LA AUTHOR_ITEM_PUBLISHED
----------- --------- ------------------------------------A101        20-FEB-05 Magazine Article
A102        20-FEB-05 Magazine Article
A103        20-FEB-05 Magazine Article
A104        20-FEB-05 Magazine Article
A105        20-FEB-05 Magazine Article
A106        20-FEB-05 Magazine Article
A107        20-FEB-05 Magazine Article
A108        20-FEB-05 Magazine Article
A109        20-FEB-05 Magazine Article
A110        20-FEB-05 Magazine Article 

10 rows selected. 

Notice that the author_item_published column was created not allowing NULL values.  I can do this because I specified a default value that was applied to all the current rows before I added the NOT NULL constraint.  If I did not define a default value, the ALTER TABLE command would have failed.

SQL> alter table author add (
  2     author_last_published  date default SYSDATE,
  3     author_item_published  varchar2(40) not null
  4  );
alter table author add (
            *

ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


The above text is an excerpt from:

Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

by John Garmany
 


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.