bat调用sql文件建立表空间、用户并导入表结构及表数据

首先建立bat文件链接数据库，然后调用sql文件，执行导入命令

 

sqlplus "管理员用户/密码@数据库SID as sysdba" @创建表空间.sqlimp 建立的用户/建立的用户的密码@SID file="导出的dmp文件名.dmp" fromuser="建立的用户" touser="建立的用户"

创建表空间文件

set serveroutput on ;DECLARE num NUMBER;--根据此参数判断表空间是否存在 url VARCHAR2(100);--存储已存在表空间的数据文件路径 execSql VARCHAR2(200);--动态执行语句 t_name VARCHAR2(20) := '表空间名称';--定义表空间名称 BEGIN SELECT COUNT(1) INTO num FROM dba_data_files WHERE TABLESPACE_NAME=upper(t_name);--查询表空是否存在 IF num<=0 THEN dbms_output.put_line(t_name||'表空间不存在需要创建!'); SELECT file_name INTO url FROM dba_data_files WHERE TABLESPACE_NAME='SYSTEM'; url:=substr(url,0,instr(url,'/',-1)); execSql :='CREATE TABLESPACE "'||t_name||'" NOLOGGING DATAFILE '''||url||t_name||'.ora'' SIZE 1024M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO'; execute immediate execSql;--创建表空间 dbms_output.put_line(t_name||'表空间创建成功!'); END IF; IF num>0 THEN dbms_output.put_line(t_name||'表空间已经存在不需要创建!'); execSql :='DROP TABLESPACE "'||t_name||'" including contents and datafiles'; dbms_output.put_line('删除表空间'||execSql); --execute immediate execSql;--删除表空间 SELECT file_name INTO url FROM dba_data_files WHERE TABLESPACE_NAME=upper(t_name); dbms_output.put_line(t_name||'磁盘路径：'||url); END IF; END;/@创建用户.sql

创建用户文件

DECLARE num NUMBER;--根据此参数判断用户是否存在 execSql VARCHAR2(200);--动态执行语句 usernm VARCHAR2(20):='用户名';--用户名 userpw VARCHAR2(20):='密码';--密码 uT VARCHAR2(20):='用户所属表空间';--用户所属表空间 BEGIN SELECT COUNT(1) INTO num FROM all_users WHERE username=upper(usernm);--查询用户是否存在 IF num<=0 THEN dbms_output.put_line(usernm||'用户不存在需要创建!'); execSql :='CREATE USER "'||usernm||'" PROFILE "DEFAULT" IDENTIFIED BY "'||userpw||'" DEFAULT TABLESPACE "'||uT||'" ACCOUNT UNLOCK'; dbms_output.put_line(execSql||'用户创建语句!'); execute immediate execSql;--创建用户 dbms_output.put_line(usernm||'用户创建成功!'); /**用户开始授权**/ execSql :='GRANT ADMINISTER DATABASE TRIGGER TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT SYSDBA TO "'||usernm||'" WITH ADMIN OPTION '; execute immediate execSql; execSql :='GRANT UNLIMITED TABLESPACE TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "AQ_ADMINISTRATOR_ROLE" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "AQ_USER_ROLE" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "CONNECT" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "DBA" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "EXP_FULL_DATABASE" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "IMP_FULL_DATABASE" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "RESOURCE" TO "'||usernm||'"'; execute immediate execSql; execSql :='GRANT "WM_ADMIN_ROLE" TO "'||usernm||'"'; execute immediate execSql; dbms_resource_manager_privs.grant_system_privilege(privilege_name=>'ADMINISTER_RESOURCE_MANAGER', grantee_name=>''||usernm||'', admin_option=>FALSE); dbms_output.put_line(usernm||'用户授权成功!'); /**用户结束授权**/ END IF; IF num>0 THEN dbms_output.put_line(usernm||'用户已经存在不需要创建!'); execSql :='DROP USER "'||usernm||'" CASCADE'; dbms_output.put_line('删除用户'||execSql); --execute immediate execSql; END IF; END;/exit;