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


 

HTML Text

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 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

  
 

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.