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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Altering Tables: Modifying Columns

You can make some modifications to columns, if needed.  Basically, any column can grow.  Any column can shrink if it does not contain data larger than the new size, but only empty columns can change data types.  Columns are modified using the ALTER TABLE MODIFY 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 modify
  2  (
  3    author_last_name varchar2(80),
  4    author_first_name varchar2(15)
  5  );
Table altered.
SQL> desc author
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 AUTHOR_KEY                                         VARCHAR2(11)
 AUTHOR_LAST_NAME                                   VARCHAR2(80)
 AUTHOR_FIRST_NAME                                  VARCHAR2(15)
 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) 
In the example above, we extended the size of author_last_name to 80 characters and reduced the size of author_first_name to 15 characters.  If we try to reduce author_first_name smaller than the largest name in the table, the command will fail.
SQL> alter table author modify (author_first_name varchar2(5));
alter table author modify (author_first_name varchar2(5))
*ERROR at line 1:

ORA-01441: cannot decrease column length because some value is too big 

All data in the database is stored in tables, and so far we have covered in detail how to retrieve the data from a table.  In this chapter, we talked about creating and maintaining tables.  Now it is time to cover putting data into tables and changing the data already in tables.  This is referred to as data manipulation and involves the INSERT, UPDATE and DELETE statements.  We want to start with getting the data into the table, so we start with the INSERT statement.

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


Oracle performance tuning software 

Oracle performance tuning book


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



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

Oracle® is the registered trademark of Oracle Corporation.