Change Oracle Charactor Set

Reference (Chinese)
http://www.itpub.net/redirect.php?fid=2&tid=1020288&goto=nextnewset

c:\>sqlplus "/ as sysdba"
 
SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 10:52:30 2003
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area   76619308 bytes
Fixed Size                   454188 bytes
Variable Size              58720256 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
 
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> set linesize 120
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active
 
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists
-- oracle 9i cannot covert clob type to another charactor set
 
SQL>
 
-- check alert<sid>.log to find which table that clob belongs to 
 
ALTER DATABASE CHARACTER SET ZHS16GBK
SYS.METASTYLESHEET (STYLESHEET) - CLOB populated
ORA-12716 signalled during: ALTER DATABASE CHARACTER SET ZHS16GBK...
 
-- there are 2 case, user table and system table
-- for user table we can export table, drop table, convert database and import table again
-- for system table we can deal it with the following steps
 
SQL> truncate table Metastylesheet;
 
Table truncated.
 
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
 
Database altered.
 
-- under 9.2.0, after convert , run catmet.sql to rebuild Metastylesheet
 
SQL> @?/rdbms/admin/catmet.sql
 
-- you had better csscan database before convertion.

csscan