Change BYTE to CHAR for all columns, all tables

When you setup your varchar2 fields without any specification CHAR or BYTE, the default value specified in nls_length_semantics is used. Which is BYTE by default.

https://docs.oracle.com/cd/E24693_01/server.11203/e24448/initparams149.htm

Now you may encounter situations, where a whole datamodel is to be changed, because many fields are defined as BYTE where a CHAR is more useful. The following script can be used for changing all tables, all columns from BYTE to CHAR. Use at own risk...


set pages 0
set lines 999
set trimspool on
set heading off

spool modify_BC.tmp

SELECT 'alter table '||owner||'.'|| table_name ||
       ' modify '||column_name||' varchar2('|| 
       data_length ||' CHAR); '
  FROM all_tab_columns
 WHERE data_type = 'VARCHAR2'
   AND char_used = 'B'
   and owner in ( 'SCOTT','HR')
;

spool off
set heading on

--now run @modify_BC.tmp