shrinking a column in oracle

Question

Lets say i have a table with the following definition

create table dummy (col1 number(9) not null)

All the values in this dummy.col1 are 7 digit long. Now i want to reduce the length of this column from 9 - 7 using alter command. Oracle gives me error that column to be modified must be empty to decrease precision or scale. Makes sense.

I want to ask is there any work around to reduce the column size?

• I can't delete the values in the column.
• I can't copy values from this column to another since it has trillions of data.

Answer 1

The column size has no relationship to how the data is physically stored (they are variable length)

e.g. '23' in a number(2) will take exactly the same space if stored in a number(38)

It is purely a constraint on the maximum number that can be stored in the column therefore you could just add a constraint on the column:

ALTER TABLE dummy ADD 
CONSTRAINT c1
CHECK (col1 < 9999999)
ENABLE
VALIDATE;

if you want it to go a little quicker change VALIDATE to NOVALIDATE obviously this will not check the validity of the existing data.

Answer 2

Kevin's answer is excellent.

The only other way to do it is to

rename the existing column,
create a new column with the old name and the new size,
issue an update statement to populate the new field (which you said you cannot do)
and then drop the renamed column.

Are you sure you cannot find some downtime one weekend to perform this task ?