decrease column size

[leonard905]

Hi all,
How can you decrease the size of a column by keeping the data intact. I have a field that I oversized (data in field is less than field size) to 12 characters and I want to reduce the field size to 10 charatcers. Any ideas with examples are greatly appreciated.


Thanks,
Leonard905

[pipo]

AFAIK, column must be empty for you to change it's size ... guess you'll have to export your data in a temp table

[marist89]

12 down to 10? I wouldn't waste my time. Especially if it is VARCHAR2...

[mageri]

create table x as select * from y;

truncate table y;

alter table x modify c1 number(10);

insert into x select * from y;

[pipo]

be careful with x and y, you messed up a bit
alter table and insert into is on y, not x

[UNIX DBA]

IF DB = Oracle8i then

alter table table_name modify column_name varchar2(10);

if the data in every is 10 or less characters I think that will work.

[amar]

Originally posted by UNIX DBA
IF DB = Oracle8i then

if the data in every is 10 or less characters I think that will work.

It will not work. As pipo has already said, the col needs to be empty for this statement to work.

-amar

[mageri]

Originally posted by pipo
be careful with x and y, you messed up a bit
alter table and insert into is on y, not x

Uupp! Sorry, that was a bad advise from me.

[UNIX DBA]

Ok, add a column,

alter table table_name add test_column varchar2(10);

Then do an update

update table
set test_column = old_column;

update table
set old_column = null;

Then drop the old column

alter table table_name drop column old_column;


Or you could try using export as suggested.

But you can definetly achieve what you want without too much bother

[elaine3839]

Pipo--

I happened to have the same need to decrease the column sizes from 4000 to 2000 so I tried your suggestion.

What I did is I exported the original tables data using Oracle's export utility and truncate that table. After that, I modified that table to decrease the column sizes. And finally, I tried to import data using Oracle's Import utility. However, the import failed because it tried to recreate the table while the table's definition is already there.

2 questions:
1. When you say 'Export to a temp table' in your previous reply, did you mean using Oracle Export utility OR by doing create table x as select * from y?

2. How can I only import data but not schema when I use Oracle's Import utility?

Thanks!