How to change character set from 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.

Before doing this activity it is recommended to take backup first.

$sqlplus / 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:

No comments:

Post a Comment