本篇內(nèi)容介紹了“Oracle字符集從GBK升級到Utf8的方法是什么”的有關(guān)知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓小編帶領(lǐng)大家學習一下如何處理這些情況吧!希望大家仔細閱讀,能夠?qū)W有所成!
創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),阿巴嘎企業(yè)網(wǎng)站建設(shè),阿巴嘎品牌網(wǎng)站建設(shè),網(wǎng)站定制,阿巴嘎網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,阿巴嘎網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè),幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力??沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿,時刻以成就客戶成長自我,堅持不斷學習、思考、沉淀、凈化自己,讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
配置--主機--primary-new-qpdb
lsnrctl stop LISTENER
export ORACLE_SID=qppri ps -ef|grep $ORACLE_SID|grep -v ORA_|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
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;
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;
alter database recover managed standby database cancel; shutdown immmediate;
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=all
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 on
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;
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 ;
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;
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
運行腳本創(chuàng)建非加密視圖:
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 視圖失效,引用的表對象不存在,無需創(chuàng)建 dw_user.qpmjc_login_info 視圖失效,引用的表對象不存在,無需創(chuàng)建 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; 授權(quán)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 <<ASDEOF set timing on exec dbms_stats.GATHER_DATABASE_STATS(options => 'gather',degree => 16); exit; ASDEOF
lsnrctl start LISTENER
配置---主機----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 缺少加密函數(shù)
OWNER OBJECT_NAME SUBOBJECT_NAME STATUS ------------------ --------------------- -------------------------- ------- DW_QPDB CIF_USER_LOG INVALID DW_QPDB CIF_USER INVALID ... ... 115 rows selected.
獲取dw_user的權(quán)限:
/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 <<cyeof set echo off set termout off set linesize 1000 set pagesize 0 set feedback off set heading off set trimout on set trimspool on set long 999999 col cmd for a150 spool '/home/oracle/cy/cuser1.sql' select dbms_metadata.get_ddl('USER','$line') CMD from dual; select '/' from dual; select 'create role '||granted_role||';' CMD from dba_role_privs where grantee='$line' and granted_role not in ('SELECT_CATALOG_ROLE','CONNECT','RESOURCE','RECOVERY_CATALOG_OWNER','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE') union all select 'grant '||granted_role||' to '||grantee||' with admin option;' CMD from dba_role_privs where grantee='$line' and admin_option='YES' union all select 'grant '||granted_role||' to '||grantee||';' CMD from dba_role_privs where grantee='$line' and admin_option='NO' union all select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant option;' CMD from dba_tab_privs where grantee='$line' and grantable='YES' union all select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' CMD from dba_tab_privs where grantee='$line' and grantable='NO' union all select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' with grant option;' CMD from dba_tab_privs where grantee in (select granted_role from dba_role_privs where grantee='$line' and granted_role not in ('SELECT_CATALOG_ROLE','CONNECT','RESOURCE','RECOVERY_CATALOG_OWNER','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')) and grantable='YES' union all select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' CMD from dba_tab_privs where grantee in (select granted_role from dba_role_privs where grantee='$line' and granted_role not in ('SELECT_CATALOG_ROLE','CONNECT','RESOURCE','RECOVERY_CATALOG_OWNER','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')) and grantable='NO' union all select 'grant '||privilege||' to '||grantee||' with admin option;' CMD from dba_sys_privs where grantee='$line' and admin_option='YES' union all select 'grant '||privilege||' to '||grantee||';' CMD from dba_sys_privs where grantee='$line' and admin_option='NO' union all select 'grant '||privilege||' to '||grantee||' with admin option;' CMD from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='$line' and granted_role not in ('SELECT_CATALOG_ROLE','CONNECT','RESOURCE','RECOVERY_CATALOG_OWNER','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')) and admin_option='YES' union all select 'grant '||privilege||' to '||grantee||';' CMD from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='$line' and granted_role not in ('SELECT_CATALOG_ROLE','CONNECT','RESOURCE','RECOVERY_CATALOG_OWNER','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')) and admin_option='NO' union all select case when max_bytes=-1 then 'alter user '||username||' quota unlimited on '||tablespace_name||';' else 'alter user '||username||' quota '||max_bytes/1024/1024||'M on '||tablespace_name||';' end CMD from dba_ts_quotas where username='$line'; spool off set feedback on set heading on set termout on set echo on exit; cyeof echo "------------------------------------------ SCHEMA: ${line} BEGIN -----------------------------------------------" >> /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的方法是什么”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實用文章!
分享文章:Oracle字符集從GBK升級到Utf8的方法是什么
網(wǎng)頁路徑:http://muchs.cn/article32/ghedsc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供小程序開發(fā)、動態(tài)網(wǎng)站、企業(yè)建站、品牌網(wǎng)站制作、全網(wǎng)營銷推廣、網(wǎng)站設(shè)計公司
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)