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

ゝ一世哀愁。 2022-06-13 10:53 331阅读 0赞

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

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.

修改之后的字符集  
SQL> select \* from v$nls\_parameters where parameter='NLS\_CHARACTERSET';  
   
PARAMETER                       VALUE  
\----------------- ----------------------------  
NLS\_CHARACTERSET                UTF8  
   
SQL>

\--------end-------