Here my goal is to change the default character set from WE8ISO8859P1 to AL32UTF8 on LINUX
Remember, this was tested on my DEVELOPMENT standalone database (refer Oracle Doc before doing on PROD).
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>
SQL> SELECT SUM (a.log_space + b.data_space + c.tempspace)
"Total_DB_Size (G)"
FROM (SELECT ROUND (SUM
(BYTES/1024/1024/1024), 2) data_space
FROM dba_data_files) b,(SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space FROM v$log) a,
(SELECT
NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace FROM dba_temp_files) c;
Total_DB_Size (G)
-----------------
11.29
Do a full backup of the database because the ALTER DATABASE CHARACTER
SET statement cannot be rolled back.
SQL> select name from v$database;
NAME
---------
TEST
SQL > select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------
WE8ISO8859P1
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE
PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
----------------------------------------
AL16UTF16
SQL> select * from v$nls_parameters where parameter like
'%CHARACTERSET';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
WE8ISO8859P1
NLS_NCHAR_CHARACTERSET
AL16UTF16
16UTF16
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1
SQL> exit
$
$ expdp directory=TEST dumpfile=FULL_TEST_10112012.dmp
logfile=FULL_TEST_10112012.log full=y
Full Database backup is taken,
SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2,
'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN'))
TYPES_USED_IN
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
WE8ISO8859P1
CHAR
WE8ISO8859P1
CLOB
WE8ISO8859P1
VARCHAR2
6 rows selected.
Oracle note suggests that if the character set conversion has happened
between a 7/8 bit character set like
WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc,
then there will be data loss for clob columns which display the old
character set.
So it is best to take a full back of the database, preferably using the
tradional export utility.
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
sql> shut immediate
SQL> startup Restrict
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
Database altered.
after changing the character set bounced the database
SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2,
'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN'))
TYPES_USED_IN
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 value from NLS_DATABASE_PARAMETERS where
parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8
SQL> select
userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> SELECT VALUE FROM
NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL16UTF16
SQL> select * from
v$nls_parameters where parameter like '%CHARACTERSET';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
NOTE: we can
use new GUI based DMU (Database Migration Assistant for Unicode)
tool to convert the NLS_CHARACTERSET of an existing database to AL32UTF8 or UTF8
How to Migrate a
WE8ISO8859P1 DB to AL32UTF8 using DMU 1.2 - an example (Doc ID 1546507.1)
