查询数据库字符集情况
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
select * from nls_database_parameters;
select * from v$nls_parameters;
select userenv('language') nls_lang from dual;
---------------------------------------------------------
服务器端字符集修改
SQL> shutdown immediate;
SQL> startup mount;
SQL> show parameter job_queue_processes;
参数值 4000 ,更改完字符集将参数改成原值
SQL> show parameter aq_tm_processes;
参数值 1 ,更改完字符集将参数改成原值
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL>ALTER SYSTEM SET JOB_QUEUE_ PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_ PROCESSES=0;
SQL> alter database open;
alter database character set ZHS16GBK; 修改字符集,但遇到超子集会报错
ORA- 12712: new character set must be a superset of old character set
出现错误提示,新字符集必须是老字符集的超集,也就原来字符集是新字符集的子集。 使用 Oracle 内部命令 internal_use 跳过使用超集检查。
SQL>alter database character set INTERNAL_USE ZHS16GBK;
更改本地字符集,并跳过超子集检测
(SQL> alter system set cluster_database=FALSE scope=spfile sid='*'; --如果是 RAC 执行可能会报 错,要执行该句,单实例不会报错,不用执行。)
(SQL>ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK; 更改国际字符集,有时 候不需更改,本次未改)
SQL> shutdown immediate;
SQL> startup mount
(SQL> alter system set cluster_database=TRUE scope=spfile sid='*'; 只有 RAC 执行,如果是 RAC 在该位置执行该句)
SQL> alter system set job_queue_processes=4000;
SQL> alter system set aq_tm_processes=1;
SQL> show parameter cluster_database
SQL> show parameter job_queue_processes;
SQL> alter database open;
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; SQL> select * from nls_database_parameters;
SQL> select * from v$nls_parameters;
SQL> select userenv('language') nls_lang from dual;
------------------------------------------------------
客户端修改字符集
在 /home/oracle 与 /root 用 户 目 录 下 的 .bash_profile 中 , 添 加 或 修 改 export
NLS_ LANG="AMERICAN_AMERICA.UTF8" 语句
关闭当前 ssh 窗口
-----------------------------------------------------
修改 PDB 字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> show pdbs;
SQL> alter pluggable database pdb1 close;
SQL> alter pluggable database pdb1 open read write restricted;
SQL> alter session set container=pdb1;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
-------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> alter database character set internal_use utf8;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
------------------------
AMERICAN_AMERICA.UTF8
SQL> alter pluggable database pdb1 close;
SQL> alter pluggable database pdb1 open;
SQL> conn /as sysdba
SQL> show pdbs;
SQL> alter pluggable database all save state;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> alter session set container=pdb1;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------
AMERICAN_AMERICA.UTF8