Oracle使用SQL脚本创建表空间，用户，分配权限

一.背景

拿到客户现场的一个oracle数据库备份，要在公司服务器上创建相应的数据库，还原数据库，以便程序开发之用。在服务器上安装好Oracle数据库，创建了对应的数据库实例后， 就需要创建表空间，创建数据库用户，并给数据库用户分配相关的权限了。于是写了个简单的脚本来完成这个工作， 以后如果又要做类似的工作时， 简单修改下脚本就可以了。

二.思路

弹不上思路，就是几个简单的步骤：

1. 声明变量， 存放表空间名称，用户名，密码，数据文件路径等， 以后只要修改这些信息， 就可以创建不同的数据库信息了。

2. 判断表空间，用户名在数据库中是否已经存在， 是否需要删除已经存在的表空间和用户信息

3. 创建表空间

4. 创建用户，指定默认表空间

5. 给用户分配权限

三.示例

示例中的表空间，用户名，密码等信息不是真实的值， 实际项目中应当使用有意义的值。

/**使用SQL脚本创建表空间，用户，分配权限@author chengjiarong@date Auguest 21st. 2012数据库信息:         用户名：test_username          密码：123456       表空间：test_namespace          数据文件：D:\data\oradata\orcl\test_namespace.dbf          初始大小: 100m       增长幅度：10mPS:  a.新建的表空间名称最好和备份文件dmp的表空间名称一致， 因子如果表中含有BLOB类型字段时，表空间的名称必须是一致的，不然imp导入会出错  b.表空间名称重命名方法：alter tablespace test_namespace rename to new_namespace;**/-- 1. 声明变量：declare--用户名usrName varchar2(20) := 'test_username';--密码pwd varchar2(20) := '123456';--表空间名称tsName varchar2(20) := 'test_namespace';--数据文件存储路径dfPath varchar2(300) := 'D:\data\oradata\orcl\test_namespace.dbf';--临时字符串变量tmpStr varchar2(300);--是否删除已经存在的用户和表空间isDelUsrAndTs boolean := true;begin-- 2. 删除已存在的用户和表空间 if isDelUsrAndTs thentmpStr := 'drop user '||usrName||' cascade';execute immediate tmpStr;tmpStr := 'drop tablespace '||tsName||' including contents and datafiles';execute immediate tmpStr;end if;-- 3. 创建表空间tmpStr := 'create tablespace '||tsName||' datafile '''||dfPath||''' size 100m autoextend on next 10m maxsize unlimited';execute immediate tmpStr;-- 4. 创建用户，分配默认表空间tmpStr := 'create user '||usrName||' identified by '||pwd||' default tablespace '||tsName;execute immediate tmpStr;-- 5. 给用户分配权限tmpStr := 'grant connect,resource,dba to '||usrName;execute immediate tmpStr;end;