oracle导入导出--批处理

1.默认获取本机IP（支持XP与WIN7系统获取IP）

2.可对远程服务器进行备份还原

3.多用户备份还原

4.操作时智能提示及IP验证

 将以下代码另存为.bat格式文件。

@echo offREM ======================================================REM backup or revert ORCL databaseREM ======================================================echo -------------------------------------------------------------------------  echo backup or revert ORCL databaseecho -------------------------------------------------------------------------  echo.  title BACKUP ORCL DATABASEcolor 0aset ORACLE_PASSWORD=ORCLset DBUSER_SYSTEM=systemset BACKUP_DIR=%cd%set TODAY=%date:~0,4%_%date:~5,2%_%date:~8,2%rem user exists default 0(0.not exist,1.exist) set USER_EXISTS=0rem invalidIP is invalid IP labels,check IP validity,if the IP is invalid then set invalidIP=1set invalidIP=0set num=1rem XP系统下获取IP:XPIP@echo offsetlocal enabledelayedexpansionset "Space=        "set "IP_addr=%Space%IP Address"for /f "tokens=1,* delims=." %%i in ('ipconfig /all') do (   for %%a in (IP_addr) do (      if "%%i"=="!%%a!" set %%a=%%j   ))Rem ===========对IP结果进行处理===========set ORACLE_HOST=%IP_addr:*:=%:ltrimif "%ORACLE_HOST:~0,1%"==" " set ORACLE_HOST=%ORACLE_HOST:~1%&&goto ltrimif not "%ORACLE_HOST%" equ "IP Address" goto doitrem WIN7系统下获取IP:WIN7IP@echo offsetlocal enabledelayedexpansionset "Space=   "set "IP_addr=%Space%IPv4 地址 "for /f "tokens=1,* delims=." %%i in ('ipconfig /all') do (   for %%a in (IP_addr) do (      if "%%i"=="!%%a!" set %%a=%%j   ))Rem ===========对IP结果进行处理===========set ORACLE_HOST=%IP_addr:*:=%:delleftif "%ORACLE_HOST:~0,1%"==" " set ORACLE_HOST=%ORACLE_HOST:~1%&&goto delleft:delrightif "%ORACLE_HOST:~-5%"=="(首选) " set ORACLE_HOST=%ORACLE_HOST:~,-5%&&goto delrightgoto doit:doit@echo offif %invalidIP%==1 ( set /p ORACLE_HOST="【无效的IP，请重新输入IP】：") else (if "%ORACLE_HOST%" equ "IPv4 地址 " (set /p ORACLE_HOST="【获取IP失败，请手动输入安装服务器IP】：") else ( set /p ORACLE_HOST="【请输入安装服务器IP，回车默认IP：%ORACLE_HOST%】："))if "%ORACLE_HOST%" equ ""  goto doitecho %ORACLE_HOST%|findstr "^[0-9]*\.[0-9]*\.[0-9]*\.[0-9]*$">nul||goto failset _inputIp=%ORACLE_HOST:.= %call :checkIp %_inputIp%:portset /p ORACLE_PORT="【请输入数据库端口 回车默认[1521] 】："set /p ORACLE_DB="【请输入数据库实例名 回车默认[ORCL] 】："set /p SYSTEM_PWD="【请输入数据库system用户密码 回车默认[ORCL]】："if "%ORACLE_PORT%" equ ""  ( set ORACLE_PORT=1521)if "%ORACLE_DB%" equ ""  ( set ORACLE_DB=ORCL)if "%SYSTEM_PWD%" equ ""  ( set SYSTEM_PWD=ORCL)set BACK_NAME=%ORACLE_DB%_%TODAY%:ChooseHandleecho ┌─────────────────────┐echo ::   (1) exp ORCL data                → 1   ::echo ::   (2) imp ORCL data                → 2   ::echo ::   (E) Exit                          → E   ::echo └─────────────────────┘echo.:InputChoice@echo offecho ------------Do your choice-----------------set /p chs=please input your choice : echo ------------End of Choice------------------echo.if "%chs%" EQU "" goto InputChoiceif %chs% EQU 1  goto expInitif %chs% EQU 2  goto impInitif "%chs%"=="E" ( goto End ) else ( echo error choice, please input your choice again! goto InputChoice):expInitset BACK_FULL_NAME=%BACKUP_DIR%\%BACK_NAME%set BACK_TODAY_DMP=%BACK_FULL_NAME%\dmpset BACK_TODAY_LOG=%BACK_FULL_NAME%\logif not exist %BACKUP_DIR% mkdir %BACKUP_DIR%if not exist %BACK_FULL_NAME% mkdir %BACK_FULL_NAME%if not exist %BACK_TODAY_DMP% mkdir %BACK_TODAY_DMP%if not exist %BACK_TODAY_LOG% mkdir %BACK_TODAY_LOG%call :queryUser:ESTEP1:: user1set ORACLE_USERNAME=user1goto expData:ESTEP2:: user2set ORACLE_USERNAME=user2goto expData:ESTEP3:: user3set ORACLE_USERNAME=user3goto expData:ESTEP4:: user4set ORACLE_USERNAME=user4goto expData:ESTEP5:: user5set ORACLE_USERNAME=user5goto expData:ESTEP6:: user6set ORACLE_USERNAME=user6goto expData:ESTEP7:: user7set ORACLE_USERNAME=user7goto expData:ESTEP8:: user8set ORACLE_USERNAME=user8goto expData:ESTEP9goto compress:expDataREM  %BACK_FULL_NAME%_bat_exp.logtype "%BACKUP_DIR%"\select_orcl_user.log | findstr /i "%ORACLE_USERNAME%" >nulif %errorlevel%==0 ( set USER_EXISTS=1 ) else (set USER_EXISTS=0)if %USER_EXISTS%==1 (echo ==================开始备份%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据=================echo ==================开始备份%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据================= >>%BACK_FULL_NAME%_bat_exp.logecho 当前的时间是： %DATE% %time% echo 当前的时间是： %DATE% %time% >>%BACK_FULL_NAME%_bat_exp.logexp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@//%ORACLE_HOST%:%ORACLE_PORT%/%ORACLE_DB% buffer=64000 file=%BACK_TODAY_DMP%\%ORACLE_USERNAME%.dmp rows=Y STATISTICS=none compress=N log=%BACK_TODAY_LOG%\%ORACLE_USERNAME%_exp.logecho ==================备份%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据完成=================echo ==================备份%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据完成================= >>%BACK_FULL_NAME%_bat_exp.log) else (echo %ORACLE_HOST%服务器 %ORACLE_USERNAME%用户不存在echo %ORACLE_HOST%服务器 %ORACLE_USERNAME%用户不存在 >>%BACK_FULL_NAME%_bat_exp.log)set /a num=%num%+1goto ESTEP%num%:compressecho .echo 备份完成! echo 备份完成! >>%BACK_FULL_NAME%_bat_exp.logecho 当前的时间是： %DATE% %time% >>%BACK_FULL_NAME%_bat_exp.logmove %BACK_FULL_NAME%_bat_exp.log %BACK_FULL_NAME%echo .goto End:impInitset REVERT_TODAY_LOG=%BACKUP_DIR%\%BACK_NAME%\implogset /p REVERT_PATH=please input revert path;enter default path[%BACKUP_DIR%\%BACK_NAME%\dmp]:if "%REVERT_PATH%" equ ""  ( set REVERT_PATH=%BACKUP_DIR%\%BACK_NAME%\dmp)if not exist %REVERT_TODAY_LOG% mkdir %REVERT_TODAY_LOG%call :queryUserREM imp tools:ISTEP1:: user1set ORACLE_USERNAME=user1goto impData:ISTEP2:: user2set ORACLE_USERNAME=user2goto impData:ISTEP3:: user3set ORACLE_USERNAME=user3goto impData:ISTEP4:: user4set ORACLE_USERNAME=user4goto impData:ISTEP5:: user5set ORACLE_USERNAME=user5goto impData:ISTEP6:: user6set ORACLE_USERNAME=user6goto impData:ISTEP7:: user7set ORACLE_USERNAME=user7goto impData:ISTEP8:: user8set ORACLE_USERNAME=user8goto impData:ISTEP9goto impLastOperation:impDataREM  ORACLE_DB%_%TODAY%_bat_imp.logtype "%BACKUP_DIR%"\select_orcl_user.log | findstr /i "%ORACLE_USERNAME%" >nulif %errorlevel%==0 ( set USER_EXISTS=1 ) else (set USER_EXISTS=0)if %USER_EXISTS%==1 (echo ==================开始还原%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据=================echo ==================开始还原%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据================= >>%ORACLE_DB%_%TODAY%_bat_imp.logecho 当前的时间是： %DATE% %time% echo 当前的时间是： %DATE% %time% >>%ORACLE_DB%_%TODAY%_bat_imp.logimp %ORACLE_USERNAME%/%ORACLE_PASSWORD%@//%ORACLE_HOST%:%ORACLE_PORT%/%ORACLE_DB% full=y buffer=64000 file=%REVERT_PATH%\%ORACLE_USERNAME%.dmp rows=Y ignore=Y log=%REVERT_TODAY_LOG%\%ORACLE_USERNAME%_imp.logecho ==================还原%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据完成=================echo ==================还原%ORACLE_HOST%服务器 %ORACLE_USERNAME%数据完成================= >>%ORACLE_DB%_%TODAY%_bat_imp.log) else (echo %ORACLE_HOST%服务器 %ORACLE_USERNAME%用户不存在echo %ORACLE_HOST%服务器 %ORACLE_USERNAME%用户不存在 >>%ORACLE_DB%_%TODAY%_bat_imp.log)set /a num=%num%+1goto ISTEP%num%:impLastOperationecho .echo 还原完成! echo 还原完成! >>%ORACLE_DB%_%TODAY%_bat_imp.logecho 当前的时间是： %DATE% %time% >>%ORACLE_DB%_%TODAY%_bat_imp.logecho %ORACLE_DB%_%TODAY%_bat_imp.log %BACKUP_DIR%\%BACK_NAME%move %ORACLE_DB%_%TODAY%_bat_imp.log %BACKUP_DIR%\%BACK_NAME%echo .goto End:queryUser@echo offecho set linesize 500;>>select_orcl_user.sqlecho show user;>>select_orcl_user.sqlecho SELECT USERNAME FROM ALL_USERS;>>select_orcl_user.sqlecho exit;>>select_orcl_user.sqlsqlplus "%DBUSER_SYSTEM%/%SYSTEM_PWD%@//%ORACLE_HOST%:%ORACLE_PORT%/%ORACLE_DB%" @"%BACKUP_DIR%"\select_orcl_user.sql >select_orcl_user.logif %chs% EQU 1  goto ESTEP1if %chs% EQU 2  goto ISTEP1:checkIpif "%4"=="" goto failfor %%i in (%1 %2 %3 %4) do (    if %%i gtr 255 goto fail)goto port:failset invalidIP=1goto doit:ENDecho.endlocaldel /f/q "%BACKUP_DIR%"\select_orcl_user.sqldel /f/q "%BACKUP_DIR%"\select_orcl_user.logpauseexit