Before Oracle 23ai, the maximum allowed columns in a table is 1000. From Oracle 23ai you can increase this value by modifying a parameter MAX_COLUMNS up to 4096 if you have any use case. This initialization parameter can be set at system level only and in case of PDB you can limit to specific PDB.
To use this MAX_COLUMN parameter the compatibility should be set to 23.0.0.0 or higher. To increase max allowed colums you must set the MAX_COLUMNS value to “EXTENDED”. Note that you can change MAX_COLUMNS from STANDARD to EXTENDED any time but to change the value back to STANDARD only when any table or view in the database contains 1000 or fewer columns.
By default, the MAX_COLUMNS initialization parameter is set to STANDARD
SQL>show parameters max_columns
NAME TYPE VALUE
-------------------------------- ----------- -------------------------
max_columns string STANDARD
When user trying to add columns more than 1000 in a table will receive below error
ORA-01792: maximum number of columns in a table or view is 1000
You can change MAX_COLUMNS value using below command that will allow up to 4096 columnsSQL> shutdown immediate;
SQL> startup
If the database has tables with more than 1000 columns and trying to update the MAX_COLUMNS parameter value back to STANDARD, then user should receive below error
SQL> ALTER SYSTEM SET set MAX_COLUMNS =STANDARD scope=spfile;
ORA-32017: failure in updating SPFILE
ORA-60471: max_columns cannot be set to STANDARD as there are one or more objects with more than 1000 columns
The only way user can change this value by dropping the objects with more than 1000 columns.
Note that older Oracle client versions (before Oracle 23ai) do not support columns more than 1000 in a table and only Oracle 23ai clients support the 4096 column limit.
Thanks & Regards,
Skip the clinic queues and get your blood test at home done from the comfort of your home. Our certified professionals provide reliable, hygienic, and timely blood collection services right at your doorstep. Whether it's a routine checkup or a specific health test, we ensure fast results with full privacy and care. Book your home blood test today and take control of your health with ease!
ReplyDeleteOh wow, I actually ran into this column limit issue during a project last year and had no idea Oracle 23ai introduced the MAX\_COLUMNS tweak—wish I had this info back then! Reminds me of when I had to juggle a crazy complex data model *and* write three assignments in the same week… thank goodness for Nursing Essay Writing Service UK or I would’ve totally missed my deadline 😅. Bookmarking this for future reference!
ReplyDeleteThis is a great update for anyone working with very wide tables in Oracle! I remember hitting that frustrating 1000-column limit in a legacy data migration project — had to split the table and create awkward joins just to make it work. Knowing that Oracle 23ai now supports up to 4096 columns by setting `MAX_COLUMNS=EXTENDED` is a huge relief for certain edge cases. Just a heads-up though, for anyone managing academic or research databases, if you're documenting data structures this wide, you might want to pair it with a Dissertation Writing Service UAE to help keep your thesis organized 😅
ReplyDelete