Thanks for the question, Arabinda.
Asked: May 22, 2024 - 6:10 am UTC
Last updated: May 23, 2024 - 2:44 am UTC
Version: Oracle 19C
Viewed 1000+ times
You Asked
I want to change the column size of a column from varchar2(100) to varchar2(50) on a non-partitioned table with 1138 Million records, yes 1.1 Billion. This project is not using Partitioned tables, so can't change the table structure.
Could you please let me know a quicker approach other than alter statement to modify column and dbms_redefinition?
and Connor said...
Data stored in a varchar2(100) occupies no more space than a varchar2(50), so if you just want to stop people adding data more than 50 chars, maybe just add a constraint
alter table T add constraint CHK check ( length(col) <= 50 ) enable novalidate;
That is instantaneous and achieves the goal no?
And I'm confused by your dbms_redefinition comment - it might take a while, but the downtime is negligible so who cares who long it takes?