sql - How to alter a column's length (in character) in Oracle 10G?

Question

I am using Oracle 10 G, I want to decrease a column size from 30 to 20 characters. Table name is member, column is member_name, it's a varchar2(30) data type.

My question: is there any query to do it? If so, what is it?

question from:https://stackoverflow.com/questions/65516851/how-to-alter-a-columns-length-in-character-in-oracle-10g

Answer 1

You can not directly decrease the size of the VARCHAR2 column if it has some data.

You need to

1. create new column with decreased size.
2. Fill the data in new column
3. remove the old column
4. rename the new column

queries should be as follows:

alter table member add member_name_new varchar2(20);
update member set member_name_new = member_name;
alter table member drop column member_name;
alter table member rename column member_name_new to member_name;