把Oracle 11g 字符集WE8MSWIN1252换成UTF8

[zhang@zhangshibo /]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 15 15:24:04 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> connect sys/orcl as sysdba;
Connected.

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

SQL> select name,value$ from props$ where name like '%NLS%';

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
UTF8

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
UTF8

NLS_RDBMS_VERSION
11.2.0.1.0

20 rows selected.

SQL>

SQL>

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size                  2214056 bytes
Variable Size            1174407000 bytes
Database Buffers          536870912 bytes
Redo Buffers                6836224 bytes
Database mounted.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set INTERNAL_USE UTF8;

Database altered.

SQL> update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';

1 row updated.

SQL>

改变字符集后，原来已有的数据不会改变，只是之后新增的数据会是新的字符集。