0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @OldProgrammer, Answer you mentioned as duplicate do not provide the sufficient details and infact That is different than this question. (In that question, OP asked to increase the size of the column and in this question OP asked to decrease the size -- both scenario need different solution). – Popeye Dec 31 '20 at 05:30

2 Answers2

1

You can alter the lengths of string columns in place. But you get an error if existing values are too big. So, just shorten the column first:

update t
    set col = substr(col, 1, 20)
    where length(col) > 20;

Then:

alter table t modify x varchar2(20)

Here is a db<>fiddle.

I strongly recommend this approach because it is compatible with existing constraints and indexes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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;
Popeye
  • 35,427
  • 4
  • 10
  • 31