Tag Archives: Modify column datatype

Modify column datatype in Oracle table

Modify column datatype in Oracle table

Modify the table column Datatype

alter table table_name modify column_name datatype;

Example: We will take an example of TEST1 table in database

SQL> desc test1;
Name Null? Type
------------ -------- -----------
ID_TEMP NUMBER(2)
ID NUMBER(1)

SQL> alter table test1 modify id number(10);
Table altered.

SQL> alter table test1 modify id number(5);
alter table test1 modify id number(5)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale

Alter multiple columns in one command

alter table table_name
modify
(
column1_name column1_datatype,
column2_name column2_datatype,
);

Example:

SQL> alter table test1 modify ( id number(11), id_Temp number(3));
Table altered.

You can also specify the constraint with modify command

SQL> alter table test1 modify ( id number(11) not null, id_Temp number(3));
Table altered.

Advertisements