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.
