Friday, May 30, 2014

Change Oracle DB 11g Character Set to AL32UTF8

Oracle11g: how I change character set to AL32UTF8?



SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

=>Shutdown your database using below command

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

=>Startup your database in restrict mode

SQL> startup restrict
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             562037200 bytes
Database Buffers          503316480 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.

=>Then Apply alter command for changing character set

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

=>Verifying after changes New Character Set

SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
  2  decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'
),
  3  9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
  4  96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
  5  112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
  6  from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order
 by CHARACTERSET;

CHARACTERSET                             TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16                                NCHAR
AL16UTF16                                NCLOB
AL16UTF16                                NVARCHAR2
AL32UTF8                                 CHAR
AL32UTF8                                 CLOB
AL32UTF8                                 VARCHAR2

6 rows selected.

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8


=>Shutdown and Restart Database for use.



No comments:

Post a Comment