@echo off rem 相关参数设置 set OracleConnectString=system/qrwl@Orcl set DataFile=D:\OraDataFile\DataFile\ set TempFile=D:\OraDataFile\TempFile\ :menu_content echo 批处理功能列表 :menu_select echo ---------------------------------------- echo (1):创建数据库 echo (11):创建表空间 echo (12):创建临时表空间 echo (13):创建用户 echo (2):恢复数据库 echo (21):恢复用户_highway_storage echo (22):恢复用户_highway_running echo (23):恢复用户_highway_history echo (24):恢复用户_highway_selldata echo (25):恢复用户_highway_exchange echo (3):删除数据库 echo (31):删除表空间 echo (32):删除临时表空间 echo (33):删除用户 echo (4):备份数据库 echo (41):备份用户_highway_storage echo (42):备份用户_highway_running echo (43):备份用户_highway_history echo (44):备份用户_highway_selldata echo (45):备份用户_highway_exchange echo (X):退出 echo ---------------------------------------- if exist temp.sql del /s/q temp.sql>nul set select= set /p select=请选择下列编号: if /i "%select%" == "1" goto Event_CreateDataBase if /i "%select%" == "11" goto Event_CreateDataBase11 if /i "%select%" == "12" goto Event_CreateDataBase12 if /i "%select%" == "13" goto Event_CreateDataBase13 if /i "%select%" == "2" goto Event_RestoreDataBase if /i "%select%" == "21" goto Event_RestoreDataBase21 if /i "%select%" == "22" goto Event_RestoreDataBase22 if /i "%select%" == "23" goto Event_RestoreDataBase23 if /i "%select%" == "24" goto Event_RestoreDataBase24 if /i "%select%" == "25" goto Event_RestoreDataBase25 if /i "%select%" == "3" goto Event_DeleteDataBase if /i "%select%" == "31" goto Event_DeleteDataBase31 if /i "%select%" == "32" goto Event_DeleteDataBase32 if /i "%select%" == "33" goto Event_DeleteDataBase33 if /i "%select%" == "4" goto Event_BackupDataBase if /i "%select%" == "41" goto Event_BackupDataBase41 if /i "%select%" == "42" goto Event_BackupDataBase42 if /i "%select%" == "43" goto Event_BackupDataBase43 if /i "%select%" == "44" goto Event_BackupDataBase44 if /i "%select%" == "45" goto Event_BackupDataBase45 if /i "%select%" == "x" goto end cls echo. echo 请选择正确的菜单编号 goto menu_select :Event_CreateDataBase echo ---------------------------------------- echo 正在创建数据库... if not exist %DataFile% md %DataFile% if not exist %TempFile% md %TempFile% sqlplus %OracleConnectString% @10_创建数据库.sql goto menu_select :Event_CreateDataBase11 echo ---------------------------------------- echo 正在创建表空间... if not exist %DataFile% md %DataFile% if not exist %TempFile% md %TempFile% echo @ScriptFile\CreateTablespaces.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_CreateDataBase12 echo ---------------------------------------- echo 正在创建临时表空间... if not exist %DataFile% md %DataFile% if not exist %TempFile% md %TempFile% echo @ScriptFile\CreateTablespaces_Temporary.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_CreateDataBase13 echo ---------------------------------------- echo 正在创建用户... if not exist %DataFile% md %DataFile% if not exist %TempFile% md %TempFile% echo @ScriptFile\CreateUsers.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_RestoreDataBase echo ---------------------------------------- call 20_恢复数据库.bat %OracleConnectString% goto menu_select :Event_RestoreDataBase21 echo ---------------------------------------- echo create or replace procedure highway_storage.droptables_sequences_views >temp.sql echo is >>temp.sql echo v_sql varchar2(4000); >>temp.sql echo cursor curtable is select table_name from user_tables order by table_name;>>temp.sql echo cursor cursequence is select sequence_name from user_sequences order by sequence_name; >>temp.sql echo begin >>temp.sql echo for currrow in curtable loop >>temp.sql echo v_sql := 'drop table ' ^|^| currrow.table_name ^|^| ' cascade constraints'; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo for currrow in cursequence loop >>temp.sql echo v_sql := 'drop sequence ' ^|^| currrow.sequence_name; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo end droptables_sequences_views;>>temp.sql echo />>temp.sql echo execute highway_storage.droptables_sequences_views();>>temp.sql echo drop procedure highway_storage.droptables_sequences_views;>>temp.sql echo quit;>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql imp %OracleConnectString% file=datafile\highway_storage.dmp rows=y fromuser=(highway_storage) touser=(highway_storage) destroy=y ignore=y commit=y log=datafile\highway_storage_imp.log goto menu_select :Event_RestoreDataBase22 echo ---------------------------------------- echo create or replace procedure highway_running.droptables_sequences_views >temp.sql echo is >>temp.sql echo v_sql varchar2(4000); >>temp.sql echo cursor curtable is select table_name from user_tables order by table_name;>>temp.sql echo cursor cursequence is select sequence_name from user_sequences order by sequence_name; >>temp.sql echo begin >>temp.sql echo for currrow in curtable loop >>temp.sql echo v_sql := 'drop table ' ^|^| currrow.table_name ^|^| ' cascade constraints'; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo for currrow in cursequence loop >>temp.sql echo v_sql := 'drop sequence ' ^|^| currrow.sequence_name; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo end droptables_sequences_views;>>temp.sql echo />>temp.sql echo execute highway_running.droptables_sequences_views();>>temp.sql echo drop procedure highway_running.droptables_sequences_views;>>temp.sql echo quit;>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql imp %OracleConnectString% file=datafile\highway_running.dmp rows=y fromuser=(highway_running) touser=(highway_running) destroy=y ignore=y commit=y log=datafile\highway_running_imp.log goto menu_select :Event_RestoreDataBase23 echo ---------------------------------------- echo create or replace procedure highway_history.droptables_sequences_views >temp.sql echo is >>temp.sql echo v_sql varchar2(4000); >>temp.sql echo cursor curtable is select table_name from user_tables order by table_name;>>temp.sql echo cursor cursequence is select sequence_name from user_sequences order by sequence_name; >>temp.sql echo begin >>temp.sql echo for currrow in curtable loop >>temp.sql echo v_sql := 'drop table ' ^|^| currrow.table_name ^|^| ' cascade constraints'; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo for currrow in cursequence loop >>temp.sql echo v_sql := 'drop sequence ' ^|^| currrow.sequence_name; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo end droptables_sequences_views;>>temp.sql echo />>temp.sql echo execute highway_history.droptables_sequences_views();>>temp.sql echo drop procedure highway_history.droptables_sequences_views;>>temp.sql echo quit;>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql imp %OracleConnectString% file=datafile\highway_history.dmp rows=y fromuser=(highway_history) touser=(highway_history) destroy=y ignore=y commit=y log=datafile\highway_history_imp.log goto menu_select :Event_RestoreDataBase24 echo ---------------------------------------- echo create or replace procedure highway_selldata.droptables_sequences_views >temp.sql echo is >>temp.sql echo v_sql varchar2(4000); >>temp.sql echo cursor curtable is select table_name from user_tables order by table_name;>>temp.sql echo cursor cursequence is select sequence_name from user_sequences order by sequence_name; >>temp.sql echo begin >>temp.sql echo for currrow in curtable loop >>temp.sql echo v_sql := 'drop table ' ^|^| currrow.table_name ^|^| ' cascade constraints'; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo for currrow in cursequence loop >>temp.sql echo v_sql := 'drop sequence ' ^|^| currrow.sequence_name; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo end droptables_sequences_views;>>temp.sql echo />>temp.sql echo execute highway_selldata.droptables_sequences_views();>>temp.sql echo drop procedure highway_selldata.droptables_sequences_views;>>temp.sql echo quit;>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql imp %OracleConnectString% file=datafile\highway_selldata.dmp rows=y fromuser=(highway_selldata) touser=(highway_selldata) destroy=y ignore=y commit=y log=datafile\highway_selldata_imp.log goto menu_select :Event_RestoreDataBase25 echo ---------------------------------------- echo create or replace procedure highway_exchange.droptables_sequences_views >temp.sql echo is >>temp.sql echo v_sql varchar2(4000); >>temp.sql echo cursor curtable is select table_name from user_tables order by table_name;>>temp.sql echo cursor cursequence is select sequence_name from user_sequences order by sequence_name; >>temp.sql echo begin >>temp.sql echo for currrow in curtable loop >>temp.sql echo v_sql := 'drop table ' ^|^| currrow.table_name ^|^| ' cascade constraints'; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo for currrow in cursequence loop >>temp.sql echo v_sql := 'drop sequence ' ^|^| currrow.sequence_name; >>temp.sql echo dbms_output.put_line(v_sql); >>temp.sql echo execute immediate v_sql; >>temp.sql echo end loop;>>temp.sql echo end droptables_sequences_views;>>temp.sql echo />>temp.sql echo execute highway_exchange.droptables_sequences_views();>>temp.sql echo drop procedure highway_exchange.droptables_sequences_views;>>temp.sql echo quit;>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql imp %OracleConnectString% file=datafile\highway_exchange.dmp rows=y fromuser=(highway_exchange) touser=(highway_exchange) destroy=y ignore=y commit=y log=datafile\highway_exchange_imp.log goto menu_select :Event_DeleteDataBase echo ---------------------------------------- echo 正在删除数据库... sqlplus %OracleConnectString% @30_删除数据库.sql goto menu_select :Event_DeleteDataBase31 echo ---------------------------------------- echo 正在删除表空间... echo @ScriptFile\DropTableSpace.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_DeleteDataBase32 echo ---------------------------------------- echo 正在删除临时表空间... echo @ScriptFile\DropTableSpace_Temporary.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_DeleteDataBase33 echo ---------------------------------------- echo 正在删除用户... echo @ScriptFile\DropUsers.sql>temp.sql echo quit>>temp.sql sqlplus %OracleConnectString% @temp.sql del temp.sql goto menu_select :Event_BackupDataBase echo ---------------------------------------- echo 正在备份数据库... if not exist DataFile md DataFile call 40_备份数据库.bat %OracleConnectString% goto menu_select :Event_BackupDataBase41 echo ---------------------------------------- if not exist DataFile md DataFile exp %OracleConnectString% owner=(highway_storage) rows=y file=datafile\highway_storage.dmp log=datafile\highway_storage_exp.log goto menu_select :Event_BackupDataBase42 echo ---------------------------------------- if not exist DataFile md DataFile exp %OracleConnectString% owner=(highway_running) rows=y file=datafile\highway_running.dmp log=datafile\highway_running_exp.log goto menu_select :Event_BackupDataBase43 echo ---------------------------------------- if not exist DataFile md DataFile exp %OracleConnectString% owner=(highway_history) rows=y file=datafile\highway_history.dmp log=datafile\highway_history_exp.log goto menu_select :Event_BackupDataBase44 echo ---------------------------------------- if not exist DataFile md DataFile exp %OracleConnectString% owner=(highway_selldata) rows=y file=datafile\highway_selldata.dmp log=datafile\highway_selldata_exp.log goto menu_select :Event_BackupDataBase45 echo ---------------------------------------- if not exist DataFile md DataFile exp %OracleConnectString% owner=(highway_exchange) rows=y file=datafile\highway_exchange.dmp log=datafile\highway_exchange_exp.log goto menu_select :end @echo on