How to change the
character set of the database.
1. Check the current
database character set.
|
1
2
3
|
select value
from nls_database_parameters
where parameter='NLS_CHARACTERSET';
|
2. Sometimes it could
be not enough – here is the query to check ALL database character sets.
|
1
2
3
4
5
6
7
|
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);
|
3. Log into the
database and do a clean shutdown of the database.
|
1
|
SHUTDOWN
IMMEDIATE;
|
If for whatever
reason, the database does not get shut down cleanly (via a shutdown immediate
command), start it back up in restrict mode and shut it down again.
Note --Do a full backup of the database because the
ALTER DATABASE CHARACTER SET statement cannot be rolled back.
4. Mount the database.
|
1
|
STARTUP
MOUNT;
|
5. Restrict logon to
the database, disable job processes and queue processes.
|
1
2
3
|
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
|
6. Open the database.
|
1
|
ALTER DATABASE OPEN;
|
7. Change the
character set (instead of &CHARSET use the proper character set, e.g.
‘EE8MSWIN1250’).
|
1
|
ALTER DATABASE CHARACTER SET
INTERNAL_USE &CHARSET;
|
8. You can also change
the national character set (instead of &NCHARSET use the proper character
set, e.g. ‘AL16UTF16’)
|
1
|
ALTER DATABASE NATIONAL CHARACTER SET
INTERNAL_USE &NCHARSET;
|
9. Make a clean
shutdown of the database.
|
1
|
SHUTDOWN
IMMEDIATE;
|
10. Start it up.
|
1
|
STARTUP;
|
Caution! Changing the character set can sometimes cause data loss or
data corruption.
I strongly encourage to make a full backup of the database, before attempting to migrate the data to a new character set.
I strongly encourage to make a full backup of the database, before attempting to migrate the data to a new character set.
