数据库相互连接字符集不同,可以这样 1、在被转换的服务器上建立一个视图,用utl_raw.cast_to_raw转换相应的字段(如有中文的字段) create view v_test as select utl_raw.cast_to_raw(name) from test; 2、在当前数据库上用utl_raw.cast_to_varchar2就行了,如: select utl_raw.cast_to_varchar2(name) from v_test@your_dblink;另外,如果不直连,可以导出后修改数据文件试试
我收藏的一篇文章:在oracle中修改字符集oracle 8i/9i里修改字符集的方法: SHUTDOWN IMMEDIATE; -- make sure there is a database backup you can rely on, or create one STARTUP MOUNT; 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 <new_character_set>; -- a alter database takes typically only a few minutes or less, -- it depends on the number of columns in the database, not the -- amount of data. SHUTDOWN; -- If you use Oracle8 then also do: STARTUP RESTRICT; SHUTDOWN;oracle 10g则有所变动: 1)执行 RDBMS\ADMIN\csminst.sql 2)用cscan 命令来检测是否可以做转换 CSSCAN SYSTEM/AAA FULL=y FROMCHAR=ZHS16CGB231280 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1 3)执行 RDBMS\ADMIN\CSALTER.PLB 来更新字符集 需要注意的是,第3)步是依赖于第2)步的。第2)步指定了欲转换的字符集 4)重启数据库 shutdown immediate; startup;
1、在被转换的服务器上建立一个视图,用utl_raw.cast_to_raw转换相应的字段(如有中文的字段)
create view v_test as select utl_raw.cast_to_raw(name) from test;
2、在当前数据库上用utl_raw.cast_to_varchar2就行了,如:
select utl_raw.cast_to_varchar2(name) from v_test@your_dblink;另外,如果不直连,可以导出后修改数据文件试试
SHUTDOWN IMMEDIATE;
-- make sure there is a database backup you can rely on, or create one
STARTUP MOUNT;
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 <new_character_set>;
-- a alter database takes typically only a few minutes or less,
-- it depends on the number of columns in the database, not the
-- amount of data.
SHUTDOWN;
-- If you use Oracle8 then also do:
STARTUP RESTRICT;
SHUTDOWN;oracle 10g则有所变动:
1)执行 RDBMS\ADMIN\csminst.sql
2)用cscan 命令来检测是否可以做转换
CSSCAN SYSTEM/AAA FULL=y FROMCHAR=ZHS16CGB231280 TOCHAR=ZHS16GBK ARRAY=1024000 PROCESS=1
3)执行 RDBMS\ADMIN\CSALTER.PLB 来更新字符集
需要注意的是,第3)步是依赖于第2)步的。第2)步指定了欲转换的字符集
4)重启数据库
shutdown immediate;
startup;