Tales From A Lazy Fat DBA

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER                      VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;
PARAMETER                      VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

Modify profile and add environmental  variable – NLS_LANG
[root@localhost ~]# vi /etc/profile
NLS_LANG=’AMERICAN_AMERICA.we8iso8859p1′; export NLS_LANG

[oracle@localhost ~]$ echo $NLS_LANG
AMERICAN_AMERICA.we8iso8859p1

To change the database character set, perform the following steps:

1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Complete the following statements:

1. STARTUP MOUNT;
2. ALTER SYSTEM ENABLE RESTRICTED SESSION;
3. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
4. ALTER SYSTEM SET AQ_TM_PROCESSES=0;
5. ALTER DATABASE OPEN;
6. ALTER DATABASE CHARACTER SET new_character_set;

SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;

STARTUP;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> ALTER DATABASE CHARACTER SET WE8ISO8895P1;
ALTER DATABASE CHARACTER SET WE8ISO8895P1
*
ERROR at line 1:
ORA-12715: invalid character set specified

If getting below provided error while change perform following steps to fix:
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

Resolution:
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE JA16SJIS;
Database altered.

SQL> SELECT * FROM nls_database_parameters WHERE parameter = ‘NLS_CHARACTERSET’;

PARAMETER
——————————
VALUE
——————————————————————————–
NLS_CHARACTERSET
JA16SJIS
—– DONE—-