Change NLS_LENGTH_SEMANTICS to CHAR

Problem

------------------

Maximo 7.6 was installed and planned to be used for multilingual environment but later we noticed that NLS_LENGTH_SEMANTICS of the oracle database was set to BYTE. This is going to be a problem in usage and getting support from IBM. How do I change to CHAR, without recreating database and restoring all data.

Solution

----------------

Two scripts and you are done

Alter database to use new NLS_LENGTH_SEMATICS.

alter system set NLS_LENGTH_SEMANTICS = CHAR scope = both;

Alter all the VARCHAR column definition

select table_name, column_name, data_type, data_length, 'ALTER TABLE '||table_name||' MODIFY '||column_name||' '||data_type||'('||data_length||' CHAR);' change_sql

from cols where data_type like '%VARCHAR%';

 

Share this post

---

Popular posts