Oracle – change database character set

SELECT * FROM nls_database_parameters;

How to change:

ALTER DATABASE CHARACTER SET AL32UTF8; To change the database character set, perform the following steps: Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0; A
LTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET new_character_set;
SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL; STARTUP

 

xxxxxxxxxxxx
1) SHUTDOWN IMMEDIATE;

2) STARTUP RESTRICT;

3) ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

4) ALTER SYSTEM DISABLE RESTRICTED SESSION;

5) select value from NLS_DATABASE_PARAMETERS where PARAMETER = ‘NLS_CHARACTERSET’;

5th step should return the value AL32UTF8

xxxxxxxxxxxx
select value
from nls_database_parameters
where parameter=’NLS_CHARACTERSET’;

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.

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;

This entry was posted in Uncategorized. Bookmark the permalink.