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/