-
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
-
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]
-
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)
-
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.
-
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);
-
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.
-
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
-
if some are already null, do:
update emp
set col5 = NULL
where col5 is not null;
Man, you really need to learn SQL. :-(
-
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.
-
thanks for the help. I am still learning. I really apreciate that.
