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.


1 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.

  • I didn't think I could modify a column that had data in it?
    – SeanGaff
    Commented Aug 10, 2023 at 12:02
  • 2
    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.
    – Littlefoot
    Commented Aug 10, 2023 at 12:56
  • 1
    @SeanGaff Making things in Oracle bigger is easy. Making things smaller - that's hard.
    – pmdba
    Commented Aug 10, 2023 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.