Database Solutions

Oracle National Character set change Changing Character Set or National Character Set are vary rare activity which DBA need to perform. In This blog, We will discuss about changing of National Character Set. We would like to change NLS_NCHAR_CHARACTERSET parameters for our database: current value - AL16UTF16 New required value - AL32UTF8 Below are the Steps followed to change the Database National Character Set: For Oracle 9i Database: 1) export all the user N-data (this may included 'SYSMAN' data) select distinct OWNER, TABLE_NAME from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') and OWNER not in ('SYS','SYSTEM', 'XDB'); 2)drop/truncate all the user N-data  -- If you do not drop all N-data then you will run into -- ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists 3) run csscan to check if everything is o csscan FULL=Y TONCHAR=UTF8 LOG=check CAPTURE=Y ARR...

Oracle Character set change A Character set does not define languages it defines a certain range of characters. Any language that uses only the characters known by that character set can then be stored. Even Oracle says that If you change character sets there is a possibility that characters that you currently use are not defined in the new character set or that the current setup is not correctly used and therefore you could lose data when changing the NLS_CHARACTERSET. Refer the following note for Migrating character set for an Oracle Applications database: Note 124721.1 (Migrating an Applications Installation to a New Character Set .) (In case of any issues/doubts it’s better to log an SR for assistance.)   You should always check this by using the Character Set Scanner (Csscan) before making any changes to your character set. Even when using Exp/imp or Expdp/Impdp. Note 458122.1 Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner) Note 745809...