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 
-9

c.確保無使用者會話、無事務:

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 hh24: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
=all

g.查出存在直方圖統計資訊的列：

export NLS_LANG=American_America
.AL
32UTF8






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

h.刪除資料

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 <<ASDEOF



set timing on


exec dbms_stats.GATHER_DATABASE_STATS(options 
=> 
'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 <<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