Modifying Column Type in Table
Method 1 :
- Add a Copy of the Column appending to some identifier to say its a copy i.e _CP
- Update the New Column to the Column whose datatype is to be changed
- Drop the Old Column
- Rename the New Column to Old Column Name
ALTER TABLE TB1 ADD COL_CP VARCHAR2(10); UPDATE TB1 SET COL_CP = COL1; ALTER TABLE TB1 DROP COLUMN COL1; ALTER TABLE TB1 RENAME COLUMN COL_CP to COL1;
In case you are upgrading from Varchar2(250) to Varchar2(4000) the above is not required
Method 2 :
- Create a New table(TB2) with the same Columns and Type
- Insert the Values into TB2
- Truncate or Drop Table TB1
- Run the Changes such as New DDL Script
- Insert into new Table TB1 from TB2
- Drop Table TB2
CREATE TABLE TB1(COL1 NUMBER); INSERT INTO TB1 VALUES(10); CREATE TABLE TB2 AS SELECT * FROM TB1; DROP TABLE TB1; . . New table Creation Script with New Column Types . . . INSERT INTO TB1 SELECT * FROM TB2; DROP TABLE TB2;