Exp/Imp & Load Data

为了帮助开发团队,在每次版本发布之前做回归测试,需要重新创建一个schema,该schema的数据库结构基于已有schema MMO, 但是新的schema业务数据需要重新导入,这些业务数据已经有一批insert sql 脚本,在导入过程需要考虑外键约束。
Baseline schema : MMO
New schema : MR
在winxp通过cmd命令操作Oracle客户端sqlplus,步骤如下:
[@more@]
1_EXPORT_MMO_NODATA.BAT
rem --start--
set admin_account=MMO/PASSWORD@MS
set DMPFILENAME=MMO_NODATA
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
exp %admin_account% file=%DMPFILENAME%.DMP LOG=EXP_%DMPFILENAME%.log rows=n
move %DMPFILENAME%.DMP .exp_imp
move EXP_%DMPFILENAME%.log .log
echo.
echo *********************************************
echo Completed to export DMP file, please find log file to check if any problem exists!
echo *********************************************
pause
rem --end--
2_RE-CREATE_MR_SCHEMA.BAT
rem --start--
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus sys/pw@ms as sysdba @./sqlscript/re_create_MR_schema.sql
@echo off
echo.
echo *********************************************
echo The MR schema has been re-created!
echo Then you can re-login with MR user.
echo *********************************************
pause
rem --re_create_MR_schema.sql
rem --Drop MR schema and re-create user MR,then grant related prilege same as MMO.
rem --end--
3_IMPORT_MR.BAT
rem --start--
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set IMPORT_ACC=MR/MR@MS
set TOUSER=MR
set FROMUSER=MMO
echo admin account: %IMPORT_ACC%
set DMPFILE=.exp_impMMO_NODATA.DMP
imp %IMPORT_ACC% file=%DMPFILE% fromuser=%FROMUSER% touser=%TOUSER% ignore=y LOG=IMP_%TOUSER%.log commit=Y
move IMP_%TOUSER%.log .log
echo.
echo *********************************************
echo Completed to import DMP file, please find log file to check if any problem exists!
echo If no problem, you can compare the schema with MMO in TOAD.
echo If something different,pls run Sync Script,but you can ignore "BIN$" objects!
echo *********************************************
pause
rem --end--
4_Remove_All_Data.cmd
rem --start--
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus MR/MR@MS @./sqlscript/remove_all.sql
@echo off
echo.
echo *********************************************
echo All tables data have been removed!
echo And you can view the log located in ./log/Remove_All.log!
echo *********************************************
pause
rem --end--
--* File Name : remove_all.sql
--* Description : Diasable all foreign key and then truncate all tables.
SET PAGESIZE 0
SET LINESIZE 160
SPOOL ./log/disable_fk.sql
SELECT 'ALTER TABLE ' || table_name || ' disable CONSTRAINT ' || constraint_name || ';'
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R';
SPOOL OFF
SPOOL ./log/DisableFKResult.log
@./log/disable_fk.sql
SPOOL OFF
SPOOL ./log/truncate_table.sql
select 'truncate table '||table_name||';' from user_tables;
SPOOL OFF
SPOOL ./log/Remove_All.log
@./log/truncate_table.sql
SPOOL OFF
SET PAGESIZE 14
SET LINESIZE 80
quit
--*
5_Load_All_Data.cmd
rem --start--
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
sqlplus MR/MR@MS @./sqlscript/execute_all.sql
@echo off
echo.
echo *********************************************
echo All tables data have been reloaded!
echo And you can view the log located in ./log/execute_all.log!
echo *********************************************
pause
rem --end--
--* File Name : execute_all.sql
--* Description : Run specified sql statement and then enable all foreign key.
set define off
spool ./log/execute_all.log
@@AB_X1.sql
... ...
... ...
... ...
@@CD_X9.sql
commit;
spool off
SET PAGESIZE 0
SET LINESIZE 160
SPOOL ./log/enable_fk.sql
SELECT 'ALTER TABLE ' || table_name || ' enable CONSTRAINT ' || constraint_name || ';'
FROM user_constraints
WHERE CONSTRAINT_TYPE = 'R';
SPOOL OFF
SPOOL ./log/EnableFKResult.log
@./log/enable_fk.sql
SPOOL OFF
SET PAGESIZE 14
SET LINESIZE 80
quit
--*

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12472709/viewspace-1035235/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/12472709/viewspace-1035235/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值