思想:
1,设置Linux字符集 为UTF8编码
2,设置Oracle字符集 为UTF8编码
3,设置sqlplus客户端环境 为UTF8编码
4,如果是在Windows使用CRT/Xshell远程工具,请设置软件编码为UTF8[略...]
1,先设置oracle为UTF8
[oracle@oracle11 ~]$ sqlplus /nolog
SQL> conn /as sysdba;
SQL> shutdown immediate;
SQL> startup mount;
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;
SQL> alter database character set internal_use utf8;
SQL> shutdown immediate;
SQL> startup;
SQL> conn scott/11
SQL> set linesize 299;
SQL> set pagesize 299;
SQL> select * from v$nls_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
---------------- -----
NLS_CHARACTERSET UTF8
SQL> select userenv('language') from dual;
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 "export NLS_LANG=\"AMERICAN_AMERICA.UTF8\"" >> .bashrc
[oracle@oracle11 ~]$ . .bashrc
[oracle@oracle11 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.UTF8
[oracle@oracle11 ~]$ sqlplus scott/11
SQL> set linesize 299;
SQL> set pagesize 299;
SQL> create table student (sid number(2) primary key, sname varchar2(50) not null,address varchar2(50), sex varchar2(1),age number(2));
SQL> insert into student values(1,'宋江','梁山好汉','m',20);
SQL> select * from student;
SID SNAME ADDRESS S AGE
---------- ----- --- ----------- - - ----------
1 宋江 梁山好汉 m 20
到scott原始表测试一下:
SQL> insert into dept(deptno,dname,loc) values(23,'中','国');
SQL> select * from dept;
23 中 国
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
6 rows selected.
查看表的创建过程:
使用DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME')函数
SQL> set pagesize 0;
SQL> set long 1000;
SQL> select DBMS_METADATA.GET_DDL('TABLE','STUDENT') from dual;
CREATE TABLE "SCOTT"."STUDENT"
("SID" NUMBER(2,0),
"SNAME" VARCHAR2(50) NOT NULL ENABLE,
"ADDRESS" VARCHAR2(50),
"SEX" VARCHAR2(1),
"AGE" NUMBER(2,0),
PRIMARY KEY ("SID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER
_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE D
EFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS L
OGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
SQL>