windows创建oracle用户bat脚本

注:标红的需要根据实际情况进行修改

1.  创建bat脚本,内容如下

chcp 65001

@echo off

if "%~2"=="" (

echo 错误: 缺少参数。

exit /b 1

)

echo "正在创建oracle用户, 用户名：%1 密码:%2"

set "ora_user=system"

set "ora_pass=xxxxxxxxpasswrod"

set "ora_data=D:\APP\WWQNW\ORADATA\ORCL\"

set "data_filepath=%ora_data%%1.dbf"

setlocal

:: 设置Oracle用户信息

set USERNAME=%1

set PASSWORD=%2

set DEFAULT_TABLESPACE=%1

set TEMPORARY_TABLESPACE=temp

:: 设置Oracle Home和命令行工具路径

set ORACLE_HOME=D:\app\wwqnw\product\11.2.0\dbhome_1

set PATH=%ORACLE_HOME%\BIN;%PATH%

echo "username:%USERNAME% password:%PASSWORD% tablespace:%DEFAULT_TABLESPACE% temp_tablespace:%TEMPORARY_TABLESPACE% data_filepath:%data_filepath%"

:: 创建Oracle用户

sqlplus /nolog @create_user.sql %USERNAME% %PASSWORD% %DEFAULT_TABLESPACE% %TEMPORARY_TABLESPACE% %data_filepath% %ora_user% %ora_pass%

::sqlplus /nolog @test.sql dba_data_files

:: 退出

endlocal

pause

2.创建create_user.sql,sql内容如下

-- create_user.sql
-- 接收参数
define username = '&1';
define password = '&2';
define default_tablespace = '&3';
define temporary_tablespace = '&4';
define data_filepath = '&4';
define ora_user ='&6';
define ora_pass ='&7';
 
-- 连接到数据库
conn &ora_user/&ora_pass@localhost:1521/orcl ;
-- 创建表空间 
create tablespace &default_tablespace  datafile '&data_filepath' size 50m autoextend on next 10m maxsize 30g;

-- 创建新用户
create user &username identified by &password default tablespace &default_tablespace temporary tablespace &temporary_tablespace;

alter user &username quota unlimited on &default_tablespace;
 
-- 授予权限
grant connect, resource to &username;

quit;