-
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
leonard905
leonard905@yahoo.com
-
AFAIK, column must be empty for you to change it's size ... guess you'll have to export your data in a temp table
-
12 down to 10? I wouldn't waste my time. Especially if it is VARCHAR2...
Jeff Hunter
-
create table x as select * from y;
truncate table y;
alter table x modify c1 number(10);
insert into x select * from y;
-
be careful with x and y, you messed up a bit
alter table and insert into is on y, not x
-
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.
-
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
-
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.
-
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
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|