Oracle Database Discussions

I am converting a database from WE8ISO8859P15 to AL32UTF8 and all seems to have gone well apart from the fact that some colums are 3 or 4 times bigger column size.

Following metalink - SCRIPT: Changing columns to CHAR length semantics ( NLS_LENGTH_SEMANTICS ) [ID 313175.1] , it says " Do not use the data_length column, this might result in an 3 or 4 times bigger column size than anticipated"

I have used this script as it is and I can see that it uses char_length and not data_length as in the script below. Can someone pls tell me what I am missing?


This is the script to convert from byte to char below:

conn / a sysdba
set feedback off
set verify off
set serveroutput on
set termout on
exec dbms_output.put_line('Starting build select of columns to be altered');
drop table sys.semantics$
/
create table sys.semantics$(s_owner varchar2(40),
s_table_name varchar2(40),
s_column_name varchar2(40),
s_data_type varchar2(40),
s_char_length number)
/
insert into sys.semantics$
select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner not in
('DBSNMP','MGMT_VIEW','SYSMAN','TRACESVR','AURORA$ORB$UNAUTHENTICATED',
'AURORA$JIS$UTILITY$','OSE$HTTP$ADMIN','MDSYS','MDDATA','ORDSYS',
'ORDPLUGINS','SI_INFORMTN_SCHEMA','CTXSYS','WKSYS','WKUSER','WK_TEST',
'REPADMIN','LBACSYS','DVF','DVSYS','ODM','ODM_MTR','DMSYS','OLAPSYS',
'WMSYS','ANONYMOUS','XDB','EXFSYS','DIP','TSMSYS','SYSTEM','SYS')
-- All Oracle provided users
and C.table_name = T.table_name
and C.char_used = 'B'
-- only need to look for tables who are not yet CHAR semantics.
and T.partitioned != 'YES'
-- exclude partitioned tables
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR')
-- You can exclude or include tables or schema's as you wish, by adjusting
-- "and T.owner not in" as per your requirements
/
commit
/
declare
cursor c1 is select * from sys.semantics$;
v_statement varchar2(255);
v_nc number(10);
v_nt number(10);
begin
execute immediate
'select count(*) from sys.semantics$' into v_nc;
execute immediate
'select count(distinct s_table_name) from sys.semantics$' into v_nt;
dbms_output.put_line
('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
for r1 in c1 loop
v_statement := 'ALTER TABLE "' || r1.s_owner || '"."' || r1.s_table_name;
v_statement := v_statement || '" modify ("' || r1.s_column_name || '" ';
v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
v_statement := v_statement || ' CHAR))';
-- To have the statements only uncomment the next line and comment the execute immediate
-- dbms_output.put_line(v_statement);
execute immediate v_statement;
end loop;
dbms_output.put_line('Done');
end;
/