What happens when you modify (reduce) a column’s length

alter-tabledatabase-designmigrationoracle

Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(10). So If I run this SQL statement:

ALTER TABLE FOO
    MODIFY(BAR NUMBER(10));
  • Will I be able to do that on nonempty column?
  • If so, what If there is one value greater than NUMBER(10), will oracle tell me about it?
  • Will column default values remain unchanged if previously defined?
  • Will column nullable option remain unchanged?
  • Will primary, foreign, unique key on that column remain unchanged?
  • Will constraints involving that columns remain unchanged?
  • Will indexes on that columns remain unchanged?

Is there any official documentation answering my questions?

Best Answer

The Oracle Administrators Guide says the following:

Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns) and column encryption.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

If you are modifying a table to increase the length of a column of data type CHAR, realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

The Oracle SQL Language Reference has much more detail including the following:

You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.

You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.

You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.

If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.

For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.

There is additional information and restrictions in the above documentation. Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2. You can try other changes so you will know what will happen.

--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level)) 
   FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;

--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));

--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));

--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));

The alter statements have the following output:

ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"

ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"

table FOO altered.

Reduce precision by creating a new column.

ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;
Related Question