Oracle 10g character set convert @ PC單機最強魔人

1.check database current set, AL32UTB8 is Unicode. our target is to conver to BIG5

SQL> SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

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

NLS_NCHAR_CHARACTERSET
AL16UTF16

2. procedure

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 20 14:43:13 2012

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2020552 bytes
Variable Size             285215544 bytes
Database Buffers          956301312 bytes
Redo Buffers               14753792 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
2 ;

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 ZHT16BIG5;

Database altered.

SQL> alter session set sql_trace=false;

Session altered.

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

Total System Global Area 1258291200 bytes
Fixed Size                  2020552 bytes
Variable Size             285215544 bytes
Database Buffers          956301312 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.

now the character is ZHT16BIG5.

5. check alert log...

Wed Jun 20 14:44:22 2012
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation might be adversely affected.
Completed: alter database open
Wed Jun 20 14:44:45 2012
alter database character set internal_use ZHT16BIG5
Wed Jun 20 14:45:04 2012
Updating character set in controlfile to ZHT16BIG5
Synchronizing connection with database character set information
Refreshing type attributes with new character set information
Wed Jun 20 14:45:12 2012

check data, if fail ha ha ha ~

i hope you have backup before... god bless you.