oracle字符集修改（AL32UTF8->UTF8）

author：skate
time：2012/04/10

AL32UTF8->UTF8

步骤
shutdown immediate;
startup mount;
alter session set sql_trace=true;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use utf8;
alter session set sql_trace=false;
shutdown immediate;
startup;

[root@detaildb ~]# su - oracle
[oracle@detaildb ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 6 09:55:53 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

修改之前的字符集
SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
----------------- ----------------------------
NLS_CHARACTERSET AL32UTF8

SQL>

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

Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 402654496 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7348224 bytes
Database mounted.
SQL> alter session set sql_trace=true;

Session altered.

SQL> alter system enable restricted session;

System altered.

SQL> show parameter job_queue_process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
SQL> alter system set job_queue_processes=0;

System altered.

SQL> show parameter aq_tm_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
SQL> alter database open;

Database altered.

SQL> alter database character set internal_use utf8;

Database altered.

SQL> alter session set sql_trace=flase;
alter session set sql_trace=flase
*
ERROR at line 1:
ORA-00922: missing or invalid option

SQL> alter session set sql_trace=false;

Session altered.

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

Total System Global Area 1603411968 bytes
Fixed Size 2226912 bytes
Variable Size 402654496 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7348224 bytes
Database mounted.
Database opened.
SQL>

修改之后的字符集
SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER VALUE
----------------- ----------------------------
NLS_CHARACTERSET UTF8

SQL>

--------end-------