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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.