【本文谢绝转载原文来自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>