Alter the table to change the structure

What will happen if one of the source files structure changes, and all data loading procedures and "sql loader" scripts are ready after long and busy working days. Oracle has options to change the table structure.

Altering a column

create table EPEKER.ep_test (col1 varchar(10));

insert into  EPEKER.ep_test values ('some text here');
--ORA-12899: value too large for column "EPEKER"."EP_TEST"."COL1" (actual: 14, maximum: 10)

alter table EPEKER.ep_test modify (col1 varchar2(20));
insert into  EPEKER.ep_test values ('some text here');
--1 row inserted
commit;

select * from EPEKER.ep_test;

Adding/Dropping a column

alter table EPEKER.ep_test add (col2 date);
alter table EPEKER.ep_test add (col2 date DEFAULT sysdate);

alter table EPEKER.ep_test drop (col2);

Renaming Columns

alter table EPEKER.ep_test rename column col3 to col4;

Changing Datatypes

But changing datatype of a column is not that easy. As the following example shows to change a datatype of a column, the column to be modified must be empty.

insert into EPEKER.ep_test (col3) values ('123')
commit;
alter table EPEKER.ep_test modify (col3 number)
--ORA-01439: column to be modified must be empty to change datatype

update EPEKER.ep_test set col3=NULL;
commit;
alter table EPEKER.ep_test modify (col3 number)

desc EPEKER.ep_test

Compressed Tables

You can modify and add columns to compressed tables. But dropping a column is still not supported (v10.2). To drop a column one should first MOVE NOCOMPRESS, drop the column and again MOVE COMPRESS a table.

alter table EPEKER.ep_test move compress;
alter table EPEKER.ep_test add (col3 varchar2(10));
alter table EPEKER.ep_test modify (col3 varchar2(20));

alter table EPEKER.ep_test drop (col3);
--ORA-39726: unsupported add/drop column operation on compressed tables