设置 Oracle,Sqlplus,Linux 设置为UTF8 专治中文乱码 ,查看表的

【本文谢绝转载原文来自http://990487026.blog.51cto.com】

思想:

1,设置Linux字符集 为UTF8编码

2,设置Oracle字符集 为UTF8编码

3,设置sqlplus客户端环境 为UTF8编码

4,如果是在Windows使用CRT/Xshell远程工具,请设置软件编码为UTF8[略...]

1,先设置oracle为UTF8

[oracle@oracle11~]$sqlplus/nolog
SQL>conn/assysdba;
SQL>shutdownimmediate;
SQL>startupmount;
SQL>altersystemenablerestrictedsession;
SQL>altersystemsetjob_queue_processes=0;
SQL>altersystemsetaq_tm_processes=0;
SQL>alterdatabaSEOpen;
SQL>alterdatabasecharactersetinternal_useutf8;
SQL>shutdownimmediate;
SQL>startup;
SQL>connscott/11
SQL>setlinesize299;
SQL>setpagesize299;
SQL>select*fromv$nls_parameterswherePARAMETER='NLS_CHARACTERSET';
PARAMETER	VALUE
---------------------
NLS_CHARACTERSETUTF8
SQL>selectuserenv('language')fromdual;
AMERICAN_AMERICA.UTF8
SQL>exit

2,Linux系统一般默认都是UTF8,以CentOS为例:

[oracle@oracle11~]$cat/etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

3,设置sqlpuls环境变量,测试

NLS_LANG的值来自下面这条语句:

SQL> select userenv('language') from dual;

AMERICAN_AMERICA.UTF8

[oracle@oracle11~]$cd/home/oracle/
[oracle@oracle11~]$echo"exportNLS_LANG="AMERICAN_AMERICA.UTF8"">>.bashrc
[oracle@oracle11~]$..bashrc
[oracle@oracle11~]$echo$NLS_LANG
AMERICAN_AMERICA.UTF8
[oracle@oracle11~]$sqlplusscott/11
SQL>setlinesize299;
SQL>setpagesize299;
SQL>createtablestudent(sidnumber(2)primarykey,snamevarchar2(50)notnull,addressvarchar2(50),sexvarchar2(1),agenumber(2));
SQL>insertintostudentvalues(1,'宋江','梁山好汉','m',20);
SQL>select*fromstudent;
SIDSNAMEADDRESS	S	AGE
-----------------------------------------
1宋江梁山好汉m20

到scott原始表测试一下:	
SQL>insertintodept(deptno,dname,loc)values(23,'中','国');
SQL>select*fromdept;
23中国
	10ACCOUNTING	NEWYORK
	20RESEARCH	DALLAS
	30SALES	CHICAGO
	40OPERATIONS	BOSTON
6rowsselected.

查看表的创建过程:

使用DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME')函数

SQL>setpagesize0;
SQL>setlong1000;
SQL>selectDBMS_METADATA.GET_DDL('TABLE','STUDENT')fromdual;
CREATETABLE"SCOTT"."STUDENT"
(	"SID"NUMBER(2,0),"SNAME"VARCHAR2(50)NOTNULLENABLE,"ADDRESS"VARCHAR2(50),"SEX"VARCHAR2(1),"AGE"NUMBER(2,PRIMARYKEY("SID")
USINGINDEXPCTFREE10INITRANS2MAXTRANS255
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER
_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHED
EFAULT)
TABLESPACE"USERS"ENABLE
)SEGMENTCREATIONIMMEDIATE
PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSL
OGGING
STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645

PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHE
DEFAULTCELL_FLASH_CACHEDEFAULT)
TABLESPACE"USERS"
SQL>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!