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

7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Greate article. Keep writing such kind of info on your site.
    Im really impressed by your site.
    Hey there, You have done an incredible job. I'll definitely digg it and personally recommend to my friends.
    I am sure they'll be benefited from this website.

    ReplyDelete
  3. Awesome dude. This posts are helping us like any thing. Thank you so much

    ReplyDelete
  4. One doubt: What is the data types of columns HARDWARE_TYPE and HARDWARE_OWNERSHIP_ID columns before modifying.

    Why I am asking is suppose if they are VARCHAR2 type means in that case they wont get inserted in Step 2.

    Its better if the original existing types also they mentioned for those 2 columns.

    ReplyDelete
  5. Wһat's up, thiѕ weekend is nice in support of mе, for tһе reason that this point inn time i aam reading thіs great educational article
    heгe at mmy һome.

    ReplyDelete
  6. Thanks very interesting blog!

    ReplyDelete
  7. this will not work when ur updating records of varchar to numberIts better if the original existing types also they mentioned for those 2 columns.

    ReplyDelete