Oracle alter table change column datatype

You can use the Oracle "alter table" syntax to change the data type for an existing column, as shown in this example:

To alter a LONG datatype column into a CLOB datatype you simply enter this DDL:

Of course, you must deal with existing column data.  When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.

In a traditional database you would need to follow these cumbersome steps to alter a column data type:

1 - Create the new column at the end of the table.

2 - Run an update to populate the new table column

3 - Drop the old table column

4 - Re-name the new column to the original column name

Also, starting in Oracle 11g, the new virtual column feature is useful for changing table column datatypes.

Implement Bitmap Indexes

I was called upon to troubleshoot and fix a State Police query system that was experiencing slow query performance. The system was read-only except for a 30-minute window at night for data loading. Upon inspection of the SQL, I noted complex combinational WHERE clauses:

WHERE color='BLU' and make='CHEVY' and year=1997 and doors=2;