Good friend Gwen posted this on Twitter yesterday, presumably alluding to some sort of potential issue with Postgres.
and it got me thinking: “I wonder how Oracle will go with this”.
So here’s a simple test, showing 5000 repeated ADD and DROP column commands
SQL> create table t ( x1 int, x2 int );
Table created.
SQL> begin
2 for i in 1 .. 5000 loop
3 execute immediate 'alter table t add newcol int';
4 execute immediate 'alter table t drop column newcol';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Now before you think I’m about to jump onto my high horse and bash all the non-Oracle databases out there, we also need to show an additional demo.
In the main, when you want to drop a column, we always recommend “SET UNUSED” because that is a zero-cost option to drop a column. My “DROP COLUMN” demo above is efficient because the table is empty. In a real life scenario, the data in the table must be re-written in order to physically eliminate the column data from each row. Here’s the same demo with the SET UNUSED variant.
SQL> create table t1 ( x1 int, x2 int );
Table created.
SQL> begin
2 for i in 1 .. 5000 loop
3 execute immediate 'alter table t1 add newcol int';
4 execute immediate 'alter table t1 set unused column newcol';
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 3
There is no real surprises here. A “SET UNUSED” command simply tells the dictionary that this column is no longer referenceable, but the data is still there. Hence the data dictionary keeps a hidden column reference for the “dropped” column. Every time I issue “SET UNUSED” I get another hidden column. Eventually, all those hidden columns add up to reach the table limit (1000 columns in 19c and below) and kersplat.
If you ever hit this issue (I think it would be a strange set of circumstances if you did), simply use DBMS_REDEFINITION to clean up the mess, eg
SQL> create table t ( x1 int, x2 int );
Table created.
SQL> insert into t select rownum, rownum from dual connect by level <= 100;
100 rows created.
SQL> alter table t add constraint t_pk primary key ( x1);
Table altered.
SQL> create index t_ix on t ( x2);
Index created.
SQL>
SQL> begin
2 for i in 1 .. 5000 loop
3 execute immediate 'alter table t add newcol int';
4 execute immediate 'alter table t set unused column newcol';
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 3
SQL> select count(*)
2 from user_tab_cols
3 where table_name = 'T';
COUNT(*)
----------
1000
SQL> exec dbms_redefinition.redef_table(user,'T',table_part_tablespace=>'USERS')
PL/SQL procedure successfully completed.
SQL> select count(*)
2 from user_tab_cols
3 where table_name = 'T';
COUNT(*)
----------
2
Learning is not a spectator sport 
Got some thoughts? Leave a comment