Modify column datatype in Oracle table

Syntax for modifying 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

Syntax for 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.