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.