You may be of the belief that “nobody needs more than the 1000 columns we could create in a table in 19c”. You are free the believe that, but let me tell you a little story.

There once was a project. At the height of the Telecom billing boom. Thy had implementations in multiple continents for customers whose names you are familiar with. One thing Telecom billing cannot afford is to not keep up with the flood of incoming data to bill customers for. Call Data Records.

They contains lists of data and processing them has to be at top speed. You also cannot introduce work that increases the processing power needed. If you do that, then the customers are likely to not upgrade.

This project had all these columns in a table including the columns it added as it processed the data. Now the tally stood at 990 columns and a bunch more had to be added for functionality in the upcoming release. We couldn’t go over 1000 och we couldn’t normalize due to the sheer volume of data streaming through. I was not for it but the solution ended up being one where all columns were put together in a single column per list and then also compressed.

The result was data we would not access from the database so looking at a single row required a export program only we had making adhoc SQL and reporting virtually impossible.

So yes, I have seen the pain of having to go over but being fysically limited to not do it.It is way too late for that solution but I am happy to see the ceiling being lifted.

After that walk down memory lane, let’s look at what we can do in 23ai.

drop table if exists test;
create table test (key number(4) primary key);

We now have a table with just one row. We’ll now try to add lots of columns to it just to see the standard behavior.

begin
  for i in 1 .. 4095
  loop
    execute immediate 'alter table test add (c_' || i || ' number(1) )';
  end loop;
end;
/

ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 4
01792. 00000 -  "maximum number of columns in a table or view is %s"

It of course fails eventually. Let’s see what the last column is named.

select column_name
     , column_id 
  from user_tab_cols 
 where table_name = 'TEST'
 order by column_id desc
 fetch first 1 rows only;

COLUMN_NAME COLUMN_ID
C_999       1000

As expected we could only add 999 more columns to get us to the 1000 limit we used to have.

Now let’s set the database to allow us to have even more columns.

With a SYSDBA user:

alter system set max_columns = extended scope=spfile;

With that set, restart the database.

Time to add even more columns… 🙂

begin
  for i in 1000 .. 4095
  loop
    execute immediate 'alter table test add (c_' || i || ' number(1) )';
  end loop;
end;
/

It took a while but it worked, lets now lokk at the max column name.

select column_name
     , column_id 
  from user_tab_cols 
 where table_name = 'TEST'
 order by column_id desc
 fetch first 1 rows only;

COLUMN_NAME COLUMN_ID
C_4095      4096

Alright. The table now has a 4096 columns. If we try to add one more.

alter table test add newcol number(1);

ORA-01792: högsta antalet kolumner i en tabell eller vy är 4096
01792. 00000 -  "maximum number of columns in a table or view is %s"

We’re back to ORA-1792, the only difference is that it now shows 4096 as the max number of columns.

You can now not move the setting max_columns back to standard unless all your tables has less than 1000 columns again.

Have fun, go wild. Or not. This is for edge cases only.