Skip to Main Content
  • Questions
  • Reduce column size on a very huge non-partitioned table


Question and Answer

Connor McDonald

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?

More to Explore


More on PL/SQL routine DBMS_REDEFINITION here