在Linux下修改oracle的字符集

先看看文档，明白一些原理，然后去折腾。

1Changing the Character Set After Database Creation

In some cases, you may wish to change the existing database character set. For instance, you may find that the number of languages that need to be supported in your database have increased. In most cases, you will need to do a full export/import to properly convert all data to the new character set. However, if, and only if, the new character set is a strict superset of the current character set, it is possible to use the ALTER DATABASE CHARACTER SET statement to expedite the change in the database character set. 

The target character set is a strict superset if and only if each and every codepoint in the source character set is available in the target character set, with the same corresponding codepoint value. For instance, the following migration scenarios can take advantage of the ALTER DATABASE CHARACTER SET statement because US7ASCII is a strict subset of WE8ISO8859P1, ZHS16GBK, and UTF8: 

Table 3-8 Sample Migration Scenarios 
Current Character Set  New Character Set   New Character Set is Strict Superset? 

US7ASCII                  WE8ISO8859P1                         Yes 

US7ASCII                   ZHS16GBK                                Yes 

US7ASCII                    UTF8                                      Yes 

Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled back. The syntax is: 

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>;


The database name is optional. The character set name should be specified without quotes, for example: 

ALTER DATABASE CHARACTER SET WE8ISO8859P1;


To change the database character set, perform the following steps. Not all of them are absolutely necessary, but they are highly recommended: 

SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
    <do a full backup>

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET <new_character_set_name>;
SQL> SHUTDOWN IMMEDIATE;   -- or NORMAL
SQL> STARTUP;


To change the national character set, replace the ALTER DATABASE CHARACTER SET statement with the ALTER DATABASE NATIONAL CHARACTER SET statement. You can issue both statements together if desired.

描述：数据从开发部的服务器导入到我的双向流测试。linux下的Jdk,jboss都安装好，就等部署，上周从开发部

拉了一小伙，整了半天，未果。这周直接找他们老大，应用部署完了，说明jboss安装没问题，中 文数据全是乱码

讨论了一下解决方法，开发部主张recreate database或者 把用户删除cascade，再导入数据。我的天，我想了一下

把表删除，修改字符集，重新导入。

开发部服务器字符集

 select userenv('language') from dual

 AMERICAN_CHINA.ZHS16GBK

 双向流服务器字符集

 WE8ISO8859P1

二者的关系既不是超集也不是所属子集关系，官方说修改会无效，还是试一下，想着建库，流复制，头很大。

2 参照官方还是有点头绪，也走了一些弯路。

-----------------------------------------------------------------------------------------------------

1 关闭数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2

SQL> startup nomount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size                  1219040 bytes
Variable Size             570426912 bytes
Database Buffers          587202560 bytes
Redo Buffers               15556608 bytes
Database mounted.
3

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

4SQL> alter database open;

5 alter database character set ZHS16GBK;
alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active---------N次出现这个问题

无法进行下去了。

------------------------------------------------------------------------------------------------------------------------------

6 再关闭数据库，直接startup,还是报ORA-12721的错误。

7再试，Ok

SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size                  1219040 bytes
Variable Size             570426912 bytes
Database Buffers          587202560 bytes
Redo Buffers               15556608 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

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 orcl character set internal_use ZHS16GBK;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size                  1219040 bytes
Variable Size             570426912 bytes
Database Buffers          587202560 bytes
Redo Buffers               15556608 bytes
Database mounted.
Database opened.

 

最后PL/SQL中文都能正常显示，不是乱码了。忙了快一天了，整理一下。