Oracle database: how to change varchar length of same column in multiple tables in one go?

Question

My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an INSERT, UPDATE or DELETE trigger. The script records client_identifier information so we can relate individual row changes back to users.

Recently, I began work on enhancing the script as we now have more requirements for session information than what client_identifier alone gives us. The result of this is that the text strings being generated by the script are now in certain cases exceeding 200 characters.

I therefore need a way to alter the varchar(200) in the many tables that have the columns so that they become varchar2(255) instead.

I would like to get some recommendations on what the best approach to this is please? I have read that doing this dynamically can be a bad idea, but given I have 4500 tables to work through, I'm not sure how else I can do it.

Thanks

Answer 1

Use a query like the following to generate your DDL, then run the output as a script:

select 'alter table ' || table_name || 
 ' modify ( CRBY VARCHAR2(255), MODBY VARCHAR2(255) );' 
from user_tables;

Modify the select statement or add where conditions as necessary for your situation.

• SeanGaff: I didn't think I could modify a column that had data in it?
• Littlefoot: You can't shorten it to value which is smaller than the longest value stored in that VARCHAR2 column, but - up to that - yes, you can. If you want to make it larger, no problem.
• pmdba: @SeanGaff Making things in Oracle bigger is easy. Making things smaller - that's hard.