321 lines
13 KiB
Batchfile
321 lines
13 KiB
Batchfile
@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
|
|
|