decreasing NULL columns, urgent help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: decreasing NULL columns, urgent help

  1. #1
    Join Date
    Nov 2000
    Posts
    169

    Arrow

    Hi, am kind of new to oracle and I need an immediate help.

    I have a table that has a column that is NULL and the datatype is varchar2(9).
    I want to decrease the datatype to varchar2(3) so I issued this command:

    alter table emp modify(
    ename varchar2(3) NULL;

    and I received this error message:
    ora-01441: column to be modified must be empty to decrease column length.

    So I looked under documentation's error messages but it did not really help.

    Can any body please show me the specific commands to do this(empty the column to be modified) so that I can change the varchar(9) to varcha2(3) without any problem?

    thanks

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    This is because you already have data in this column, Column has to be empty.

    Step 1 create new table

    create table emp1 as
    select col1, col2, col3,.....(except the column that you want to decrese the lenght of) from emp;

    drop table emp;

    rename table emp1 to emp;

    alter table emp
    add(col4 varchar2(3) Null);

    commit;

    Thanks
    Imad

    [Edited by irehman on 12-13-2000 at 12:17 PM]

  3. #3
    Join Date
    Nov 2000
    Posts
    169
    Is ther any way this can be done without the table being droped, because the table in question is a production table(i.e. in a production database)

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    Talking

    make a copy of that table, copy all the data there. modify the original table and then copy the data back from the "backup" table to the original.

  5. #5
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Another way of doing this is by using update.

    Lets say name of the column is Col5 and table name is emp;

    update emp
    set col5 = Null;

    commit;

    alter table emp
    add(col5 varchar2(3) Null);




  6. #6
    Join Date
    Jun 2000
    Posts
    417
    Is there a serious problem that requires it to be varchar2(3) instead of varchar2(9)? Is it worth messing with a production table?. Is the database 24x7?

    If you can hold out, just wait until you can take the database down for maintenance and follow basically what irehman suggested. You're not really dropping the table (not losing the contents anyway). Unless I misread, doing what he suggested will totally wipe out the contents of that column (which I'm not sure you want to do, just resize).

    Create a table emp1 (or some other temp name) with the complete script used to create the original emp table (you could find this from something like TOAD or just write it yourself if the script isn't lying around anywhere), but create it with the ename as varchar2(3) instead of varchar2(9).

    Make sure that all the ename columns in emp are under 3 in length so no errors will occur when you insert. If any are more than 3, do you really mean to shrink it that low? You'll have to modify the records or delete them.

    Then insert into emp1 as select * from emp2, commit.

    Ensure all the data is there, then drop table emp, and then rename emp1 to emp.

    If you can't afford to take the database down (or at least prevent access to that table) it shouldn't be a huge problem just to keep the varchar2(9) instead of varchar2(3)

    If you want to keep the data in that column, DONT update the table and set it to NULL or the data will be lost.

  7. #7
    Join Date
    Nov 2000
    Posts
    169
    thanks for all the quick responses.
    In irehman, response, it was stated that and I quote " Another way of doing this is by using update.

    Lets say name of the column is Col5 and table name is emp;

    update emp
    set col5 = Null;

    commit;

    alter table emp
    add(col5 varchar2(3) Null); "

    My question is what if the column is already NULL, do I still have to say:

    update emp
    set col5 = NULL

  8. #8
    Join Date
    Jun 2000
    Posts
    295
    if some are already null, do:
    update emp
    set col5 = NULL
    where col5 is not null;

    Man, you really need to learn SQL. :-(

  9. #9
    Join Date
    Jun 2000
    Posts
    417
    update table emp set col5 = NULL; will erase all of the data in the table for that column, not modify the definition of the column. Unless you want to erase the data (if you can afford to do that, what are you storing it for anyway?) don't do that on the table.

    On another note if it's a production table, and it's very large, you might not be able to just 'insert into emp1 as select * from emp' without either making a large rollback segment or doing it in batch sizes so you can commit between every 1000 records or something.

    You could try export/import but you would have to pre-create the table and make sure beforehand that all the sizes of the columns are less than or equal to 3. Then hope that import doesn't complain about the different table definition.

  10. #10
    Join Date
    Nov 2000
    Posts
    169
    thanks for the help. I am still learning. I really apreciate that.

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