alter table modify column in oracle database

We can change  the column datatype in a table using alter table modify column in oracle

ALTER TABLE table_name
MODIFY column_name <action>;

Action can be many things like
(a) change the data type
(b) Shorten or widen the data type
(c) Change the default value
(d) Change to allow null or not nulls
(e) Column Visibility
(d) Virtual Column Modification
Let’s start one by one

Oracle MODIFY column change data type

You can modify column change datatype only if the column is empty

SQL> CREATE TABLE Books_master
( Book_Id NUMBER(6,0),
Title VARCHAR2(50),
Author VARCHAR2(50),
ISBN VARCHAR2(25),
BCost NUMBER(6,0),
Publish_Year NUMBER(4,0),
CONSTRAINT Books_master_PK PRIMARY KEY (Book_Id),
CONSTRAINT Books_master_UK1 UNIQUE (Title, Author),
CONSTRAINT Books_master_UK2 UNIQUE (ISBN)
) ;
Table Created

Since the table is empty, we can modify column change datatype

SQL> alter table Books_master modify ISBN NUMBER(6,0);
Table altered.
SQL> alter table Books_master modify ISBN VARCHAR2(25);
Table altered.

Let’s insert some data

SQL> INSERT INTO Books_master (Book_Id, Title, Author, ISBN, BCost, Publish_Year) VALUES (10, 'Complete/Convenient', 'Ketan Bhagat', 'ISBN 978-93-80349-92-3', 195, 2013);
1 row created.
commit;

Now again try to modify the column to change the datatype

SQL> alter table Books_master modify ISBN NUMBER(6,0);
alter table Books_master modify ISBN NUMBER(6,0)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Now we can null that column and run this statement successfully

SQL> update Books_master set ISBN=null;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table Books_master modify ISBN NUMBER(6,0);
Table altered.

Now what to do if we want to keep the data, there are multiple options
(a) Add new column as new datatype, copy data to this column, delete the old column, rename new column as actual column name:

alter table books_master add ISBN1 number;
update books_master set ISBN1=to_number(ISBN);
alter table drop column ISBN;
alter table rename column ISBN1 to ISBN;

(b) Use DBMS_REDEFINITION and rebuild your table with new columns

Oracle MODIFY column Shorten or widen the datatype(alter table modify column size oracle)

This is used when we want to change the VARCHAR bytes or characters.
Example

Here we are increasing the VARCHAR length from 50 to 100

SQL> alter table Books_master modify title VARCHAR2(100);
Table altered.

Now if we are trying to shorten the length from 100 to 10

SQL> alter table Books_master modify title VARCHAR2(10);
alter table Books_master modify title VARCHAR2(10)
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big

This happens as existing data does not fit into that size. So either can increase the length or update that column

SQL> alter table Books_master modify title VARCHAR2(20);
Table altered.

Oracle MODIFY column default value

if we have to modify the default value of the column

SQL> alter table BOOKS_MASTER modify BCOST default 1000;
Table altered.

Now next time if any insert happens and BCOST is null, it will have a default value as 1000

alter table BOOKS_MASTER modify BCOST default 1000;
Table altered.

SQL> INSERT INTO Books_master (Book_Id, Title, Author, ISBN,Publish_Year) VALUES (21, 'Deception', 'Dan', 123,2001);
1 row created.
SQL> commit;
Commit complete.

SQL> select BCOST from Books_master where book_id=21;
BCOST
-----
1000

Oracle MODIFY column NULL or NOT NULL

We can modify the column to allow null or not nulls

SQL> desc books_master
Name Null? Type
---- ---   ----
BOOK_ID NOT NULL NUMBER(6)
TITLE VARCHAR2(20)
AUTHOR VARCHAR2(50)
ISBN NUMBER(6)
BCOST NOT NULL NUMBER(6)
PUBLISH_YEAR NUMBER(4)

SQL> alter table BOOKS_MASTER modify PUBLISH_YEAR not null;
Table altered.

SQL> desc books_master
Name Null? Type
---- ----  -----
BOOK_ID NOT NULL NUMBER(6)
TITLE VARCHAR2(20)
AUTHOR VARCHAR2(50)
ISBN NUMBER(6)
BCOST NOT NULL NUMBER(6)
PUBLISH_YEAR NOT NULL NUMBER(4)

If you are changing to not null, we should have existing value, else this will fail

Oracle MODIFY column visibility

We can change the column visibility with Modify column

SQL> desc books_master
Name Null? Type
---- ---- -----
BOOK_ID NOT NULL NUMBER(6)
TITLE VARCHAR2(20)
AUTHOR VARCHAR2(50)
ISBN NUMBER(6)
BCOST NOT NULL NUMBER(6)
PUBLISH_YEAR NOT NULL NUMBER(4)

SQL> alter table BOOKS_MASTER modify PUBLISH_YEAR invisible;
Table altered.

Now the column become invisible

SQL> desc books_master
Name Null? Type
---- ----  ----
BOOK_ID NOT NULL NUMBER(6)
TITLE VARCHAR2(20)
AUTHOR VARCHAR2(50)
ISBN NUMBER(6)
BCOST NOT NULL NUMBER(6)

SQL> alter table BOOKS_MASTER modify PUBLISH_YEAR visible;
Table altered.

Now the column become visible
SQL> desc books_master
Name Null? Type
--- ----- -----
BOOK_ID NOT NULL NUMBER(6)
TITLE VARCHAR2(20)
AUTHOR VARCHAR2(50)
ISBN NUMBER(6)
BCOST NOT NULL NUMBER(6)
PUBLISH_YEAR NOT NULL NUMBER(4)

I hope you like this post on the oracle alter table modify the column. Please do provide the feedback

Also Reads
alter table add column oracle
alter table rename column oracle
drop column in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3001.htm