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 
      增长幅度：10m

PS:
  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 then
tmpStr := '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;