在Linux下修改oracle的字符集(2)

因为是双向流复制，要一致的表结构，同样，把开发部服务器导入过来，乱码

考验时候来了，这次解决更快些，有些地方还得注意一下，记录一下。

先查看一下

SELECT   *   FROM   PROPS$;  实践证明：不要在这个SQL语句中 update props$ ....确实难搞。
 select * from v$nls_parameters;

Select * from NLS_DATABASE_PARAMETERS;

SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,
 DECODE(TYPE#, 1,
 DECODE(CHARSETFORM, 1, 'VARCHAR2', 2, 'NVARCHAR2', 'UNKOWN'),
 9,
 DECODE(CHARSETFORM, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
 96,
 DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
 112,
DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
 FROM SYS.COL$
 WHERE CHARSETFORM IN (1, 2)
AND TYPE# IN (1, 9, 96, 112);

我的正常的情况是：

1 ZHS16GBK CHAR
2 AL16UTF16 NCLOB
3 ZHS16GBK VARCHAR2
4 AL16UTF16 NVARCHAR2
5 ZHS16GBK CLOB
6 AL16UTF16 NCHAR

 

1进入 /etc/profile/ 编辑vi profile

添加NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

2  source  profile

3 进入oracle主目录 编辑vi .bash_profile, source .bash_profile

4

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

Total System Global Area 1048576000 bytes
Fixed Size                  1223392 bytes
Variable Size             541066528 bytes
Database Buffers          503316480 bytes
Redo Buffers                2969600 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 ysb character set internal_use ZHS16GBK;

Database altered.

SQL> alter database character set 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 1048576000 bytes
Fixed Size                  1223392 bytes
Variable Size             541066528 bytes
Database Buffers          503316480 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

重新进入PL/SQL，查看的是乱码，重校导入数据就OK了。

如要详细了解可查询ora-06552错误.