I have a table with almost 20 columns and at least one billion rows.
What if I change type of one column from varchar2(8) to varchar2(16)? There is no index, constraint or foreign key on given column. But my table has too many records. I will use below script for the operation:
ALTER TABLE user MODIFY type VARCHAR2(16);
I have done this operation in my test environment but it does not have as many records as my production environment.
As far as I can tell, it'll be instantly. It is a varchar2 column and change is being done only in data dictionary, table data isn't affected at all.
If it were char, then yes - it would take time because such a column contains data right-padded with spaces up to its full length, so you'd actually have to enlarge the column, but also perform update on a table, and that would take time if there are that many rows.