Increasing a Field Size 1

[DanAuber]

I have a table with data in it

I need to increase the size of a number field from 6 to 8.
Can I do this and keep the data intact ?
If so, what would the command be ??

thanks for any help

Dan

Dan Auber
DanAuber@aol.com

http://members.aol.com/_ht_a/danauber/myhomepage/

 

[karluk]

This can be done with an "alter table" statement. For example, if the datatype is "number" use the following:

alter table your_table modify your_column number(8);

 

[RafaelCintron]

You can increase the size of a column, but Oracle will not allow you to decrease it.
Use alter table to do it.

 

[carp]

Oracle WILL let you decrease the size of a column or even change the datatype, but you cannot have any data in the column. Traditionally, you handle this by
(1) copy the data to another table:
CREATE TABLE temp_table AS SELECT primary_key_column, column_to_be_changed
FROM mytable;
(2) get rid of the data in the column to be changed:
DELETE column_to_be_changed FROM mytable;
(3) shrink the column:
ALTER TABLE mytable MODIFY column_to_be_changed VARCHAR2(6);
(4) reload the data
UPDATE mytable m SET column_to_be_changed = (SELECT column_to_be_changed
FROM temp_table t WHERE m.primary_key_column = t.primary_key_column);
(5) Get rid of the temp table:
DROP TABLE temp_table;

However, you don't have to go through all of this if you are using Oracle 8i:
(1) Create a new column:
ALTER TABLE mytable ADD COLUMN new_column VARCHAR2(6);
(2) Move the data over:
UPDATE mytable SET new_column = column_to_be_changed;
(3) Get rid of the old column:
ALTER TABLE mytable DROP COLUMN column_to_be_changed;

Of course, this simpler method has the down side of forcing you to change the column name.

 

[RafaelCintron]

Yep. Forgot that little detail! That's why I prefer to copy the data to another table, run the script that created the table (modified), and then insert back the data again.
It's easier this way.

 

[stevecal]

Carp observed that one aspect of the 8i Drop Column method was:

>> 'Of course, this simpler method has the down side of
>> forcing you to change the column name'

But then you could merely repeat the process in reverse recycling the old columm name but using the new size, dropping the new column name...