Oracle字符集从GBK升级到Utf8的方法是什么
本篇内容介绍了“Oracle字符集从GBK升级到Utf8的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
创新互联是一家集网站建设,阿巴嘎企业网站建设,阿巴嘎品牌网站建设,网站定制,阿巴嘎网站建设报价,网络营销,网络优化,阿巴嘎网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。
1、导出前准备(单实例单监听)
a.关闭zabbix 监控
配置--主机--primary-new-qpdb
b.停应用停监听杀会话
lsnrctl stop LISTENER
杀会话:
export ORACLE_SID=qppri
ps -ef|grep
$ORACLE_SID|grep
-v ORA_|grep
LOCAL=NO|awk
'{print $2}'|xargs
kill -9c.确保无用户会话、无事务:
set linesize 1000 set pagesize 3000 col event for a30 col status for a10 col blkses for 99999 col username for a14 col module for a45 col program for a40 col machine for a25 col state for a20 col cmd for a23 col sql_id for a20 select s.inst_id,s.sid,s.event,s.state,s.status, s.last_call_et lcet,s.sql_id,s.username,c.command_name cmd,s.module,s.program,s.machine from gv$session s,gv$sqlcommand c where s.type='USER' and s.inst_id=c.inst_id and s.command=c.command_type order by s.username,s.sql_id,s.module,s.program; select 'alter system disconnect session '''||sid||','||serial#||''' immediate;' from v$session where type='USER'; alter system disconnect session '2375,38626' immediate; select inst_id,START_TIME,(sysdate-to_date(START_TIME,'mm/dd/yy hh34:mi:ss'))*24 eslaped_hours, USED_UBLK*8/1024 MB from gv$transaction order by 4,3;
d.主库切几次日志,并确保备库无延迟,设置主库远程归档路径为defer
主库：
alter system switch logfile;
备库：
col name for a20 col value for a20 col unit for a30 col TIME_COMPUTED for a20 col DATUM_TIME for a20 set linesize 1000 select * from v$dataguard_stats;
主库：
alter system set log_archive_dest_state_2='defer' scope=both;
e.备库停止应用,并关闭
alter database recover managed standby database cancel; shutdown immmediate;
f.导出数据
vi exp_qp.sh
#!/bin/bash
. /home/oracle/.bash_profile
export ORACLE_SID=qppri
export NLS_LANG=American_America.AL32UTF8
#####variable SCHES#######
SCHES="CONFIG,
DEVQ_DP,
ACCOUNT,
BOPS,
CONFIGSVR,
MESSAGE,
OPENFIRE,
MON,
IDEXCHANGE,
QPWEB,
QP_MAPI_BASE,
QP_MJCORE_BASE,
QP_CORE_BASE,
QP_CRM_BASE,
CIF_BASE,
IDEXCHANGE_BASE,
GOLD_COIN_CORE_BASE,
SS_CORE_BASE,
SS_SYN_OUT_BASE,
BENCH_CAPTCHA_BASE,
CONFIG_SERVER_DISTRIB_BASE,
DEV_BANXIA,
BOPS_BASE,
BOPS_COMMON_BASE,
OA_FRONT_BASE,
KEY_CORE_BASE,
BOPS_QP_BASE"
expdp \'/ as sysdba\'
schemas=${SCHES} directory=EXPDP_DATA
dumpfile=qpasdwsx_%U.dmp
parallel=4 logfile=expdp_qp.log
compression=allg.查出存在直方图统计信息的列：
export NLS_LANG=American_America.AL32UTF8
vi zft.sql
set echo off
set termout off
set linesize
1000
col cmd for a160
set pagesize
0
set feedback off
set heading off
set trimout
on
set trimspool
on
spool
'/home/oracle/cy/stats.sql'
select 'select '||column_name||' from '||owner||'.'||table_name||' where '||column_name||' is not null and 1=2;' cmd
from dba_tab_col_statistics
where histogram <>'NONE'
and table_name
not like 'BIN$%'
and owner
in ('CONFIG','DEVQ_DP','ACCOUNT','BOPS','CONFIGSVR','MESSAGE','OPENFIRE','MON','IDEXCHANGE','DW_QPDB','QPWEB','QP_MAPI_BASE','QP_MJCORE_BASE','QP_CORE_BASE','QP_CRM_BASE','CIF_BASE','IDEXCHANGE_BASE','GOLD_COIN_CORE_BASE','SS_CORE_BASE','SS_SYN_OUT_BASE','BENCH_CAPTCHA_BASE','CONFIG_SERVER_DISTRIB_BASE','DW_USER','DEV_BANXIA','BOPS_BASE','BOPS_COMMON_BASE','OA_FRONT_BASE','KEY_CORE_BASE','BOPS_QP_BASE');
spool off
set feedback
on
set heading
on
set termout
on
set echo
onh.删除数据
vi duser.sql drop user CONFIG cascade; drop user DEVQ_DP cascade; drop user ACCOUNT cascade; drop user BOPS cascade; drop user CONFIGSVR cascade; drop user MESSAGE cascade; drop user OPENFIRE cascade; drop user MON cascade; drop user IDEXCHANGE cascade; drop user DW_QPDB cascade; drop user QPWEB cascade; drop user QP_MAPI_BASE cascade; drop user QP_MJCORE_BASE cascade; drop user QP_CORE_BASE cascade; drop user QP_CRM_BASE cascade; drop user CIF_BASE cascade; drop user IDEXCHANGE_BASE cascade; drop user GOLD_COIN_CORE_BASE cascade; drop user SS_CORE_BASE cascade; drop user SS_SYN_OUT_BASE cascade; drop user BENCH_CAPTCHA_BASE cascade; drop user CONFIG_SERVER_DISTRIB_BASE cascade; drop user DEV_BANXIA cascade; drop user BOPS_BASE cascade; drop user BOPS_COMMON_BASE cascade; drop user OA_FRONT_BASE cascade; drop user KEY_CORE_BASE cascade; drop user BOPS_QP_BASE cascade;
i.手动删除DW_USER用户的视图：
select lOWER(OWNER)||'.'||lower(view_name) from dba_views where owner='DW_USER'; vi dview.sql drop view dw_user.qpmjc_message_push_setting ; drop view dw_user.qpmjc_login_info ; drop view dw_user.qpmjc_game_winning_rule_link ; drop view dw_user.qpmjc_game_winning_rule_config ; drop view dw_user.qpmjc_game_rule_config ; drop view dw_user.qpmjc_client_push_msg_his ; drop view dw_user.qpmjc_client_push_msg ; drop view dw_user.qpmjc_board_wall_card ; drop view dw_user.qpmjc_board_user_settle_detail ; drop view dw_user.qpmjc_board_user_settle ; drop view dw_user.qpmjc_board_user ; drop view dw_user.qpmjc_board_ro_usr_act_fbd ; drop view dw_user.qpmjc_board_round_match_group ; drop view dw_user.qpmjc_board_round_match ; drop view dw_user.qpmjc_board_round ; drop view dw_user.qpmjc_board_meld_group_card ; drop view dw_user.qpmjc_board_meld_group ; drop view dw_user.qpmjc_board_hand_card ; drop view dw_user.qpmjc_board_discard ; drop view dw_user.qpmjc_board_act_msg_seq ; drop view dw_user.qpmjc_board_act_card_link ; drop view dw_user.qpmjc_board_action ; drop view dw_user.qpmjc_board ; drop view dw_user.qpmjc_base_card_config ; drop view dw_user.qpc_room_user ; drop view dw_user.qpc_room_purchase_fund_bill ; drop view dw_user.qpc_room_property_config_link ; drop view dw_user.qpc_room_dismiss_apply ; drop view dw_user.qpc_room_dismiss_apl_user_chos ; drop view dw_user.qpc_room ; drop view dw_user.qpc_role_user_link ; drop view dw_user.qpc_role_authority_link ; drop view dw_user.qpc_role ; drop view dw_user.qpc_property_value_config ; drop view dw_user.qpc_property_config ; drop view dw_user.qpc_group_user_invite_join_his ; drop view dw_user.qpc_group_user_invite_join ; drop view dw_user.qpc_group_user_apply_join_his ; drop view dw_user.qpc_group_user_apply_join ; drop view dw_user.qpc_group_user ; drop view dw_user.qpc_group_type_config ; drop view dw_user.qpc_group_play_prop_cfg_link ; drop view dw_user.qpc_group_playway_room_link ; drop view dw_user.qpc_group_playway ; drop view dw_user.qpc_group_notice ; drop view dw_user.qpc_group ; drop view dw_user.qpc_game_sort ; drop view dw_user.qpc_game ; drop view dw_user.qpc_area_sort ; drop view dw_user.qpcm_staff_role_link ; drop view dw_user.qpcm_staff_role ; drop view dw_user.qpcm_staff_group ; drop view dw_user.qpcm_staff ; drop view dw_user.gldcoin_trans_code ; drop view dw_user.gldcoin_sub_trans_code ; drop view dw_user.gldcoin_general_account ; drop view dw_user.gldcoin_freeze_type ; drop view dw_user.gldcoin_freeze ; drop view dw_user.gldcoin_deposit_type ; drop view dw_user.gldcoin_deposit ; drop view dw_user.gldcoin_charge_biz_type ; drop view dw_user.gldcoin_account_type ; drop view dw_user.gldcoin_account_log ; drop view dw_user.gldcoin_account ; drop view dw_user.cif_user ;
2、改字符集
shutdown immediate; 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 INTERNAL_USE AL32UTF8; ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8; shutdown immediate; startup; set lines 1000; select * from nls_database_parameters; alter system set job_queue_processes=1000; alter system set aq_tm_processes=1;
3、导入数据
导入元数据
vi imp_qp.sh #!/bin/bash . /home/oracle/.bash_profile export ORACLE_SID=qppri export NLS_LANG=American_America.AL32UTF8 impdp \'/ as sysdba\' directory=EXPDP_DATA dumpfile=qpasdwsx_%U.dmp parallel=4 logfile=impdp_qp.log content=metadata_only
修改字段
因为字符集不同字段宽度不够，需要提前测试
alter table CONFIG.CFG_CN_CHAR_DICT modify value CHAR(3); alter table DATA_HANYU_CORE_BASE.HANZI modify HANZI VARCHAR2(6); ... ... alter table BOPS_BASE.BPBS_SORT_MENU modify SORT_MENU_NAME VARCHAR2(48); alter table SS_SYN_OUT_BASE.OABASE_ROLE modify ROLE_NAME VARCHAR2(48);
导入数据
impdp \'/ as sysdba\' directory=EXPDP_DATA dumpfile=qpasdwsx_%U.dmp parallel=4 logfile=impdp_qp.log exclude=statistics table_exists_action=append
创建DW_USER的视图：
运行脚本创建非加密视图：
dw_user.qpmjc_game_winning_rule_link dw_user.qpmjc_game_winning_rule_config dw_user.qpmjc_game_rule_config dw_user.qpmjc_client_push_msg_his dw_user.qpmjc_client_push_msg dw_user.qpmjc_board_wall_card dw_user.qpmjc_board_user_settle_detail dw_user.qpmjc_board_user_settle dw_user.qpmjc_board_user dw_user.qpmjc_board_ro_usr_act_fbd dw_user.qpmjc_board_round_match_group dw_user.qpmjc_board_round_match dw_user.qpmjc_board_round dw_user.qpmjc_board_meld_group_card dw_user.qpmjc_board_meld_group dw_user.qpmjc_board_hand_card dw_user.qpmjc_board_discard dw_user.qpmjc_board_act_msg_seq dw_user.qpmjc_board_act_card_link dw_user.qpmjc_board_action dw_user.qpmjc_board dw_user.qpmjc_base_card_config dw_user.qpc_room_user dw_user.qpc_room_purchase_fund_bill dw_user.qpc_room_property_config_link dw_user.qpc_room_dismiss_apply dw_user.qpc_room_dismiss_apl_user_chos dw_user.qpc_room dw_user.qpc_role_user_link dw_user.qpc_role_authority_link dw_user.qpc_role dw_user.qpc_property_value_config dw_user.qpc_property_config dw_user.qpc_group_user_invite_join_his dw_user.qpc_group_user_invite_join dw_user.qpc_group_user_apply_join_his dw_user.qpc_group_user_apply_join dw_user.qpc_group_user dw_user.qpc_group_type_config dw_user.qpc_group_play_prop_cfg_link dw_user.qpc_group_playway_room_link dw_user.qpc_group_playway dw_user.qpc_group_notice dw_user.qpc_group dw_user.qpc_game_sort dw_user.qpc_game dw_user.qpc_area_sort dw_user.qpcm_staff_role_link dw_user.qpcm_staff_role dw_user.qpcm_staff_group dw_user.qpcm_staff dw_user.gldcoin_trans_code dw_user.gldcoin_sub_trans_code dw_user.gldcoin_general_account dw_user.gldcoin_freeze_type dw_user.gldcoin_freeze dw_user.gldcoin_deposit_type dw_user.gldcoin_deposit dw_user.gldcoin_charge_biz_type dw_user.gldcoin_account_type dw_user.gldcoin_account_log dw_user.gldcoin_account dw_user.qpmjc_message_push_setting 视图失效，引用的表对象不存在，无需创建 dw_user.qpmjc_login_info 视图失效，引用的表对象不存在，无需创建 gold_coin_core_base.gldcoin_account gold_coin_core_base.gldcoin_account_log gold_coin_core_base.gldcoin_account_type gold_coin_core_base.gldcoin_charge_biz_type gold_coin_core_base.gldcoin_deposit gold_coin_core_base.gldcoin_deposit_type gold_coin_core_base.gldcoin_freeze gold_coin_core_base.gldcoin_freeze_type gold_coin_core_base.gldcoin_general_account gold_coin_core_base.gldcoin_sub_trans_code gold_coin_core_base.gldcoin_trans_code qp_core_base.qpc_area_sort qp_core_base.qpc_game qp_core_base.qpc_game_sort qp_core_base.qpc_group qp_core_base.qpc_group_notice qp_core_base.qpc_group_playway qp_core_base.qpc_group_playway_room_link qp_core_base.qpc_group_play_prop_cfg_link qp_core_base.qpc_group_type_config qp_core_base.qpc_group_user qp_core_base.qpc_group_user_apply_join qp_core_base.qpc_group_user_apply_join_his qp_core_base.qpc_group_user_invite_join qp_core_base.qpc_group_user_invite_join_his qp_core_base.qpc_property_config qp_core_base.qpc_property_value_config qp_core_base.qpc_role qp_core_base.qpc_role_authority_link qp_core_base.qpc_role_user_link qp_core_base.qpc_room qp_core_base.qpc_room_dismiss_apl_user_chos qp_core_base.qpc_room_dismiss_apply qp_core_base.qpc_room_property_config_link qp_core_base.qpc_room_purchase_fund_bill qp_core_base.qpc_room_user qp_crm_base.qpcm_staff qp_crm_base.qpcm_staff_group qp_crm_base.qpcm_staff_role qp_crm_base.qpcm_staff_role_link qp_mjcore_base.qpmjc_base_card_config qp_mjcore_base.qpmjc_board qp_mjcore_base.qpmjc_board_action qp_mjcore_base.qpmjc_board_act_card_link qp_mjcore_base.qpmjc_board_act_msg_seq qp_mjcore_base.qpmjc_board_discard qp_mjcore_base.qpmjc_board_hand_card qp_mjcore_base.qpmjc_board_meld_group qp_mjcore_base.qpmjc_board_meld_group_card qp_mjcore_base.qpmjc_board_round qp_mjcore_base.qpmjc_board_round_match qp_mjcore_base.qpmjc_board_round_match_group qp_mjcore_base.qpmjc_board_ro_usr_act_fbd qp_mjcore_base.qpmjc_board_user qp_mjcore_base.qpmjc_board_user_settle qp_mjcore_base.qpmjc_board_user_settle_detail qp_mjcore_base.qpmjc_board_wall_card qp_mjcore_base.qpmjc_client_push_msg qp_mjcore_base.qpmjc_client_push_msg_his qp_mjcore_base.qpmjc_game_rule_config qp_mjcore_base.qpmjc_game_winning_rule_config qp_mjcore_base.qpmjc_game_winning_rule_link
手动创建加密视图：
create or replace view cif_base.cif_user_view as select USER_ID, LOGIN_NAME, (encryptor(LOGIN_PASSWORD)) LOGIN_PASSWORD, (encryptor(REAL_NAME)) REAL_NAME, STATUS, (encryptor(EMAIL)) EMAIL, (encryptor(QQ)) QQ, CAN_LOGIN, CERT_TYPE, (encryptor(CERT_NO)) cert_no, substr(CERT_NO,1,6) certno6, substr(CERT_NO,-12,8) birthdaynum,substr(CERT_NO,-2,1) sexnum, GMT_CREATE, GMT_MODIFIED, EMAIL_VALIDATE, QQ_VALIDATE, (encryptor(cell)) cell, substr(cell,1,7) cell7, CELL_VALIDATE, (encryptor(ACCOUNT_PASSWORD)) ACCOUNT_PASSWORD, NICK_NAME, USER_TYPE_NAME, DOMAIN, SUB_DOMAIN, GMT_CHANGE_IDENTITY, SEX , BIRTHDAY, ONE_AUTH_ID from cif_base.cif_user with read only; grant select on cif_base.cif_user_view to dw_user; create view dw_user.cif_user as select * from cif_base.cif_user_view with read only;
以下视图本不存在，无需创建
create or replace view cif_base.cif_one_auth_view as select ONE_AUTH_ID, AUTH_NAME, AUTH_NAME_TYPE, GMT_CREATE, GMT_MODIFIED, (encryptor(CELL)) CELL, CELL_VALIDATE, DOMAIN, SUB_DOMAIN, DEFAULT_USER_ID, LOGIN_PASSWORD, CAN_LOGIN, ACCOUNT_PASSWORD, (encryptor(REAL_NAME)) REAL_NAME, (encryptor(EMAIL)) EMAIL, (encryptor(QQ)) QQ, CERT_TYPE, (encryptor(CERT_NO)) CERT_NO, EMAIL_VALIDATE, QQ_VALIDATE, GMT_CHANGE_IDENTITY, SEX, (encryptor(BIRTHDAY)) BIRTHDAY from cif_base.cif_one_auth; grant select on cif_base.cif_one_auth_view to dw_user; create view dw_user.cif_one_auth as select * from cif_base.cif_one_auth_view with read only; 授权DW_USER用户： grant CONNECT to DW_USER; grant RESOURCE to DW_USER; grant SELECT on QP_CORE_BASE.QPC_GROUP to DW_USER; grant SELECT on QP_CORE_BASE.QPC_ROOM to DW_USER; grant SELECT on QP_CORE_BASE.QPC_GROUP_USER to DW_USER; grant SELECT on QP_CORE_BASE.QPC_ROOM_PURCHASE_FUND_BILL to DW_USER; grant SELECT on QP_CORE_BASE.QPC_ROOM_USER to DW_USER; grant SELECT on QP_CORE_BASE.QPC_GROUP_PLAYWAY_ROOM_LINK to DW_USER; grant SELECT on QP_CORE_BASE.QPC_GROUP_PLAYWAY to DW_USER; grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD to DW_USER; grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER_SETTLE_DETAIL to DW_USER; grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER to DW_USER; grant SELECT on QP_MJCORE_BASE.QPMJC_BOARD_USER_SETTLE to DW_USER; grant SELECT on QP_CRM_BASE.QPCM_STAFF to DW_USER; grant SELECT on QP_CRM_BASE.QPCM_STAFF_ROLE_LINK to DW_USER; grant SELECT on QP_CRM_BASE.QPCM_STAFF_GROUP to DW_USER; grant SELECT on QP_CRM_BASE.QPCM_STAFF_ROLE to DW_USER; grant SELECT on CIF_BASE.CIF_USER_VIEW to DW_USER; grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_DEPOSIT_TYPE to DW_USER; grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT_TYPE to DW_USER; grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT to DW_USER; grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_ACCOUNT_LOG to DW_USER; grant SELECT on GOLD_COIN_CORE_BASE.GLDCOIN_DEPOSIT to DW_USER; grant UNLIMITED TABLESPACE to DW_USER;
编译失效对象:
@?/rdbms/admin/utlrp.sql select owner,object_name,subobject_name,status from dba_objects where status='INVALID';
收集统计信息：
export NLS_LANG=American_America.AL32UTF8 @/home/oracle/cy/stats.sql vi gstats.sh #!/bin/bash . /home/oracle/.bash_profile export ORACLE_SID=qppri sqlplus / as sysdba <'gather',degree => 16); exit; ASDEOF
开启监听：
lsnrctl start LISTENER
开启zabbix监控：
配置---主机----primary-new-qpdb
通知应用
导出：10分钟 采用压缩9G 导入：54min
问题：
ORA-39082: Object type ALTER_FUNCTION:"QP_MJCORE_BASE"."SPLIT" created with compilation warnings 原本就无效 ORA-39082: Object type VIEW:"CIF_BASE"."CIF_USER_VIEW" created with compilation warnings 缺少加密函数
原本无效的对象：
OWNER OBJECT_NAME SUBOBJECT_NAME STATUS ------------------ --------------------- -------------------------- ------- DW_QPDB CIF_USER_LOG INVALID DW_QPDB CIF_USER INVALID ... ... 115 rows selected.
获取dw_user的权限：
/home/oracle/cy/schemas.txt DW_USER /home/oracle/cy/cuser.sh #!/bin/bash . /home/oracle/.bash_profile export ORACLE_SID=qppri echo > /home/oracle/cy/cuserfin.sql cat /home/oracle/cy/schemas.txt|while read line; do line=`echo $line | tr '[:lower:]' '[:upper:]'` rm -f /home/oracle/cy/cuser1.sql sqlplus -s / as sysdba <> /home/oracle/cy/cuserfin.sql echo " " >> /home/oracle/cy/cuserfin.sql cat /home/oracle/cy/cuser1.sql >> /home/oracle/cy/cuserfin.sql echo " " >> /home/oracle/cy/cuserfin.sql echo "------------------------------------------ SCHEMA: ${line} END -----------------------------------------------" >> /home/oracle/cy/cuserfin.sql echo " " >> /home/oracle/cy/cuserfin.sql done
“Oracle字符集从GBK升级到Utf8的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！
文章标题：Oracle字符集从GBK升级到Utf8的方法是什么
本文地址：http://6mz.cn/article/ghedsc.html