ORA-01439: column to be modified must be empty to change datatype

ORA-01439: column to be modified must be empty to change datatype
Cause: An ALTER TABLE MODIFY statement attempted to change the datatype of a column containing data. A column whose datatype is to be altered must contain only NULL values.
Action: To alter the datatype, first set all values in the column to NULL

Last week, one of my friend encountered ORA-01439 while altering the datatype of 2 columns to NUMBER from CHAR of USER_HARDWARE_RELATIONSHIPS table. His requirement was also not to change the columns order in table. Following are the alter commands he executed.

Reproducing ORA-01439

SQL> alter table USER_HARDWARE_RELATIONSHIPS modify HARDWARE_TYPE NUMBER(9,0);
alter table USER_HARDWARE_RELATIONSHIPS modify HARDWARE_TYPE NUMBER(9,0)
                                                *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

SQL> alter table USER_HARDWARE_RELATIONSHIPS modify HARDWARE_OWNERSHIP_ID NUMBER(9,0);
alter table USER_HARDWARE_RELATIONSHIPS modify HARDWARE_OWNERSHIP_ID NUMBER(9,0)
                                                *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

How to resolve ORA-01439

I suggested him to use following simple steps to alter the datatype of columns to NUMBER from CHAR.
1) Add new columns in table to hold data temporary
2) Update new temporary columns added with Old columns data
3) Update old columns to be altered as NULL
4) Alter table old columns to new data type
5) Update old columns altered with data from temp temporary columns
6) Alter table to drop temporary columns

Before performing above mentioned steps, first we should take the count of the records to verify them before dropping the backup table.

SQL> SELECT COUNT(1), HARDWARE_TYPE FROM USER_HARDWARE_RELATIONSHIPS GROUP BY HARDWARE_TYPE ORDER BY HARDWARE_TYPE;
  COUNT(1) HARDWARE_TYPE
---------- -------------
     93320             0
      9153             1
     31717             2
     22879             3
      2673             4
      6763             5 

Step 1: Add new columns in table to hold data temporary

SQL> ALTER TABLE USER_HARDWARE_RELATIONSHIPS ADD
  2  (
  3    HARDWARE_TYPE1          NUMBER(9,0),
  4    HARDWARE_OWNERSHIP_ID1  NUMBER(9,0)
  5  );
Table altered.

Step 2: Update new temporary columns added with Old columns data

SQL> UPDATE USER_HARDWARE_RELATIONSHIPS
  2  SET HARDWARE_TYPE1     = HARDWARE_TYPE,
  3  HARDWARE_OWNERSHIP_ID1 = HARDWARE_OWNERSHIP_ID;
166505 rows updated.

Step 3: Update old columns to be altered as NULL

SQL> UPDATE USER_HARDWARE_RELATIONSHIPS
  2  SET HARDWARE_TYPE     = NULL,
  3  HARDWARE_OWNERSHIP_ID = NULL;
166505 rows updated.

Step 4: Alter table old columns to new data type

SQL> ALTER TABLE USER_HARDWARE_RELATIONSHIPS MODIFY HARDWARE_TYPE NUMBER(9,0);
Table altered.

SQL> alter table USER_HARDWARE_RELATIONSHIPS modify HARDWARE_OWNERSHIP_ID NUMBER(9,0);
Table altered.

Step 5: Update old columns altered with data from temporary columns

SQL> UPDATE USER_HARDWARE_RELATIONSHIPS
  2  SET HARDWARE_TYPE     = HARDWARE_TYPE1,
  3  HARDWARE_OWNERSHIP_ID = HARDWARE_OWNERSHIP_ID1;
166505 rows updated.

SQL> COMMIT;    
Commit complete.

Verify the record counts after update to validate the data

SQL> SELECT COUNT(1), HARDWARE_TYPE FROM USER_HARDWARE_RELATIONSHIPS GROUP BY HARDWARE_TYPE ORDER BY HARDWARE_TYPE;
  COUNT(1) HARDWARE_TYPE
---------- -------------
     93320             0
      9153             1
     31717             2
     22879             3
      2673             4
      6763             5

Step 6: Alter table to drop temporary columns

SQL> alter table USER_HARDWARE_RELATIONSHIPS drop column HARDWARE_TYPE1;
Table altered.

SQL> alter table USER_HARDWARE_RELATIONSHIPS drop column HARDWARE_ownership_id1;
Table altered.

And we are done :)




Related Posts:
- ORA-02303: cannot drop or replace a type with type or table dependents
- Oracle - DDL, DML and DCL commands
- ORA-02438: Column check constraint cannot reference other columns
- Oracle: Delete duplicate rows from table
- Oracle 11g: Virtual Column
- ORA-01403: no data found