CODEWITHSUNDEEP
sqloracle-databaserdbms
user14842715
user14842715

Reputation:

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

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?

Upvotes: 0

Views: 1412

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

Popeye
Popeye

Reputation: 35900

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;

Upvotes: 0

Related Questions