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.