10g改字符集

jl8323

又是不好好建实例，新库和老库的字符集不一样，人家就不想重建。NND。

1.查看当前数据库字符集:
SQL> select name,value$ from props$ where name like '%NLS%';
 
NAME                           VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               US7ASCII

2.关库，startup mount状态，修改字符集
 
SQL> shutdown immediate

SQL> startup mount
SQL> alter system enable restricted session;
 
System altered.
 
SQL> alter database open;
 
Database altered.
 
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
 
 
SQL> truncate table Metastylesheet;
 
Table truncated.
 
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
 
4.使用internal_convert来修改
 
SQL> alter database character set internal_convert zhs16gbk;
 
Database altered.

alert信息：
 SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered
 SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered
 SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered
 SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered
 SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered
 SYS.RULE$ (CONDITION) - CLOB representation altered
Refreshing type attributes with new character set information
Completed: alter database character set internal_convert zhs1
----
 
5.因为前面清空了SYS.METASTYLESHEET表，需要重新创建
10g中通过运行catmeta.sql脚本来重建
@?/rdbms/admin/catmeta.sql
shutdown
startup

正常。

6.但是之后做impdp发现报以下错误

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

SQL> exec dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.
解决