DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: decrease column size

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    AFAIK, column must be empty for you to change it's size ... guess you'll have to export your data in a temp table

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    12 down to 10? I wouldn't waste my time. Especially if it is VARCHAR2...
    Jeff Hunter

  4. #4
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70
    create table x as select * from y;

    truncate table y;

    alter table x modify c1 number(10);

    insert into x select * from y;

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    be careful with x and y, you messed up a bit
    alter table and insert into is on y, not x

  6. #6
    Join Date
    Sep 2001
    Posts
    112
    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.


  7. #7
    Join Date
    Mar 2001
    Posts
    314
    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

  8. #8
    Join Date
    Sep 2001
    Location
    SWEDEN
    Posts
    70

    Unhappy

    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.

  9. #9
    Join Date
    Sep 2001
    Posts
    112
    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

  10. #10
    Join Date
    May 2001
    Posts
    285
    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
  •  


Click Here to Expand Forum to Full Width