Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 128,862
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

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 )

w

Connecting to %s

 
%d bloggers like this: