Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 19 August 2014

Changing National Character Set AL16UTF16 to UTF8

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB. 
In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.
Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import).
Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g).

You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed. 
$sqlplus /nolog
SQL> CONNECT / AS SYSDBA

SQL> Select property_value from database_properties
     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name
     from dba_tab_columns
     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and
     owner != 'SYS' and owner != 'SYSTEM';
Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;
SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP 
On the other hand, oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns.
It is always not better choice to use 'ALTER DATABASE' command to change the character set. Check the Documentation for better solution:
CAUTION: We recommend that you perform a full database backup before changing the national character set.

0 comments:

Post a Comment