Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 148,962
  • Archives

  • Categories

  • Subscribe

  • Advertisements

Character Set Change.

Posted by FatDBA on August 17, 2012

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.
  1. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
  1. 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—-

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: