ALTER TABLE: changing a table that already has data

Tables change after they go live: a new column, a longer name, a rule that should have been there from the start. ALTER TABLE does it in place, with the data still there.

The test case

Copy the players table, then: add a status column that must be filled (every current player is 'ACTIVE'), widen the name to 60 characters, rename born to date_of_birth, and drop bowling_style.

Setup

begin
  for t in (select table_name from user_tables where table_name in ('D102_A', 'D102_B')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d102_a as
select player_id, team_id, player_name, role, batting_hand, bowling_style, born from cr_players;

Setup

create table d102_b as select * from d102_a;

CREATE TABLE ... AS SELECT copies the columns and rows, and the explicit NOT NULLs, but not the keys, indexes or other constraints.

Expected result: the columns afterwards

select column_id, column_name, data_type, data_length, nullable, count(*) as tables
from   user_tab_columns
where  table_name in ('D102_A', 'D102_B')
group  by column_id, column_name, data_type, data_length, nullable
order  by column_id;

Look at PLAYER_ID in the result: nullable Y. In CR_PLAYERS it can't be null, but only because it's the primary key, and keys don't survive CTAS. We found that out when this page's test first ran on 19c; the fix is alter table d102_a add primary key (player_id) after the copy.

Way 1: ADD ... DEFAULT ... NOT NULL in one go

Setup

alter table d102_a add (status varchar2(10) default 'ACTIVE' not null);

Setup

alter table d102_a modify (player_name varchar2(60));

Setup

alter table d102_a rename column born to date_of_birth;

Setup

alter table d102_a drop column bowling_style;

Adding a column with a DEFAULT and NOT NULL is instant even on a billion-row table: Oracle records the default in the dictionary instead of writing it into every row.

Way 2: add, fill, then make it required

Setup

alter table d102_b add (status varchar2(10));

Setup

update d102_b set status = 'ACTIVE';

Setup

alter table d102_b modify (status not null);

Setup

alter table d102_b modify (player_name varchar2(60));

Setup

alter table d102_b rename column born to date_of_birth;

Setup

alter table d102_b set unused (bowling_style);

Setup

alter table d102_b drop unused columns;

The long way: the UPDATE touches every row, so on a big table it's slow and generates a lot of redo. SET UNUSED hides a column instantly (it's gone as far as SQL is concerned); DROP UNUSED COLUMNS reclaims the space later, at a quiet time.

Expected result: the data afterwards

select status, count(*) as players, max(length(player_name)) as longest_name,
       min(date_of_birth) as oldest_born
from   d102_a group by status;

select status, count(*) as players, max(length(player_name)) as longest_name,
       min(date_of_birth) as oldest_born
from   d102_b group by status;

DROP, PURGE and the recycle bin

drop table d102_a;                         -- goes to the recycle bin
flashback table d102_a to before drop;     -- and comes back
drop table d102_a purge;                   -- gone for good

Without PURGE, a dropped table is renamed (BIN$...) and kept until space is needed. It has saved more careers than any backup policy.

19c and 26ai

The same on both. 26ai adds DROP TABLE IF EXISTS and ALTER TABLE ... ADD IF NOT EXISTS-style guards for scripts that run twice (page 12.7).

Gotchas

• Shrinking a column fails if any value is too long (ORA-01441). Find them first with MAX(LENGTH(col)).
• DDL commits. Any ALTER ends your transaction, committing whatever DML you had pending.
• Renaming a column breaks code that uses the old name: views become invalid, PL/SQL won't compile. Search before you rename.