ORACLE 数据库字符集相关

本电脑的NLS_LANG   SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

//查看Server端参数
select * from sys.nls_database_parameters;

//查看Client端参数
select * from sys.nls_instance_parameters;

//查看会话的参数
select * from sys.nls_session_parameters;

//查看运行参数
select * from V$NLS_PARAMETERS;

//查看数据库可用字符集
SELECT * FROM v$nls_valid_values where isdeprecated='TRUE';

//参数

NLS_CHARACTERSET -数据库字符集

NLS_NCHAR_CHARACTERSET -国家字符集

 

sys用户下有个表要改：update props$ set value$='ZHS16GBK' where name like 'NLS%SET';
注册表中有一个地方要改：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG\SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

扩大表空间：alter tablespace xxxx resize xxxxM;

 

解决方案为：把当前的NLS_LANG变量设置成database的NLS_LANGUAGE+’_‘+NLS_TERRITORY.NLS_CHARACTERSET

    1 使用以下查询语句：

select * from props$ a where a.name = 'NLS_LANGUAGE'  得到'NLS_LANGUAGE' = american

select * from props$ a where a.name = 'NLS_TERRITORY'  得到'NLS_LANGUAGE' = america

select * from props$ a where a.name = 'NLS_CHARACTERSET'   得到 'NLS_CHARACTERSET'  =  AL32UTF8

也可以用select * from v$nls_parameters where parameter='NLS_CHARACTERSET' 得到 'NLS_CHARACTERSET'  =  AL32UTF8

    2 设置当前的NLS_LANG变量

set NLS_LANG = american_america.AL32UTF8

 

 

--查询session
select username,sid,serial# from v$session;

--杀死session
alter system kill session'483,2520';

--删除User
DROP USER user_name CASCADE;

--解锁用户
alter user user_name account unlock;