SP 批量创建表空间和用户

某证券的异常交易项目的测试环境已经早没了，只有生产环境。因为都是稳定运行的项目，一般开发完直接上生产，具体遇到问题在生产上排查。但是由于流程逐渐严格，所有发包必须走流程，经测试部验证后才能上产线，所以需要搭个测试环境。但是这个项目已经很早了，关于项目的数据库用户，有框架用户、产品用户，还有客户给自己创建的用户，总共 100 + 。当时看着客户提供过来的需创建的用户，头大。所以就有了批量创建的想法。

首先需要知道，创建表空间、创建用户的语句

1
2
3
4
5
6
7
8


-- 创建表空间
CREATE TABLESPACE "TS_TEST" DATAFILE '/home/dmdba/data/TS_TEST.DBF' 
SIZE 500 AUTOEXTEND ON NEXT 40 MAXSIZE UNLIMITED;

-- 创建用户
CREATE USER "TEST" IDENTIFIED BY "123456789" DEFAULT TABLESPACE TS_TEST;

-- *** 上方使用双引号是为了区分大小写，不用双引号都默认大写 *** 

1
2
3


-- 创建表空间
CREATE TABLESPACE TABLESPACE_NAME DATAFILE '/home/ORACLE/data/TS_TEST.DBF'
SIZE 500 AUTOEXTEND ON NOLOGGING EXTENT MANAGEMENT LOCAL;

实现方式一

1.需要创建的表空间和用户使用 excel 整理好，一一对应

2.创建表，存放表空间和用户数据

1

CREATE TABLE TEST.T_TABLESPACE_USER(TABLESPACE_NAME VARCHAR2(200), USER_NAME VARCHAR2(200));

3.开锁，把整理好的数据插入表中

1

SELECT TABLESPACE_NAME, USER_NAME, ROWID FROM TEST.T_TABLESPACE_USER;

4.编写存储过程，循环创建

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43


CREATE OR REPLACE PROCEDURE TEST.P_CREATE_TABLESPACE(I_BUSI_DATE    IN VARCHAR2,
                                                     O_RETURN_MSG   OUT VARCHAR2,
                                                     O_RETURN_CODE OUT INTEGER) IS
  --=====================================================
  -- AUTHOR: WQ 
  -- MARK: 批量创建表空间\用户程序
  --=====================================================

  -- 变量
  V_DATAFILE_PATH VARCHAR2(1000) DEFAULT '/home/oracle/data/testdb/'; -- 表空间路径
  V_SIZE          VARCHAR2(20) DEFAULT '100M';	-- 表空间默认大小
  --=====================================================

  -- 业务处理过程
BEGIN

  DBMS_OUTPUT.PUT_LINE('表空间创建：开始');

  -- 遍历表空间名称
  FOR TABLESPACE_REC IN (SELECT TABLESPACE_NAME FROM TEST.T_TABLESPACE_USER)
    LOOP
      EXECUTE IMMEDIATE 'CREATE BIGFILE TABLESPACE ' ||
                        TABLESPACE_REC.TABLESPACE_NAME || ' DATAFILE ''' ||
                        V_DATAFILE_PATH || TABLESPACE_REC.TABLESPACE_NAME ||
                        '.DBF''' || ' SIZE ' || V_SIZE ||
                        ' AUTOEXTEND ON NOLOGGING EXTENT MANAGEMENT LOCAL';
    -- 输出，方便查看创建结果
    DBMS_OUTPUT.PUT_LINE('表空间 ' || TABLESPACE_REC.TABLESPACE_NAME ||
                         ' 创建成功。');
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('表空间创建：结束');

  -- 设置返回值
  O_RETURN_CODE := 1;
  O_RETURN_MSG := '执行成功';

EXCEPTION
  WHEN OTHERS THEN
    O_RETURN_CODE := SQLCODE;
    O_RETURN_MSG  := SQLERRM;
    ROLLBACK;
END;

奇怪，达梦里边报语法分析错误，不太理解。

… 没设置兼容 ORACLE 语法，把 dm.ini 的 COMPATIBLE_MODE 改为 2 即可

改正后

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66


CREATE OR REPLACE PROCEDURE TEST.P_CREATE_TABLESPACE(I_BUSI_DATE    IN VARCHAR2,
                                                     O_RETURN_MSG   OUT VARCHAR2,
                                                     O_RETURN_CODE OUT INTEGER) IS
  --=====================================================
  -- AUTHOR: WQ 
  -- MARK: 批量创建表空间\用户程序
  --=====================================================

  -- 变量
  V_DATAFILE_PATH VARCHAR2(1000) DEFAULT '/home/dmdba/data/DAMENG/'; -- 表空间路径
  V_SIZE          VARCHAR2(20) DEFAULT '100M';	-- 表空间默认大小
  --=====================================================

  -- 业务处理过程
BEGIN

DBMS_OUTPUT.PUT_LINE('表空间创建：开始');

-- 遍历表空间名称
FOR TABLESPACE_REC IN (SELECT TABLESPACE_NAME FROM TEST.T_TABLESPACE_USER)
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLESPACE ' ||
                        TABLESPACE_REC.TABLESPACE_NAME || ' DATAFILE ''' ||
                        V_DATAFILE_PATH || TABLESPACE_REC.TABLESPACE_NAME ||
                        '.DBF''' || ' SIZE ' || V_SIZE ||
                        ' AUTOEXTEND ON NEXT 40 MAXSIZE UNLIMITED';
      -- 输出，方便查看创建结果
      DBMS_OUTPUT.PUT_LINE('表空间 ' || TABLESPACE_REC.TABLESPACE_NAME || ' 创建成功。');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('表空间 ' || TABLESPACE_REC.TABLESPACE_NAME || ' 创建失败。错误信息：' || SQLERRM);
    END;
  END LOOP;

DBMS_OUTPUT.PUT_LINE('表空间创建：结束');

DBMS_OUTPUT.PUT_LINE('用户创建：开始');

-- 遍历用户名称
FOR USER_REC IN (SELECT TABLESPACE_NAME, USER_NAME FROM TEST.T_TABLESPACE_USER)
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE USER ' ||
                        USER_REC.USER_NAME || ' IDENTIFIED BY ''123456789'' ' ||
                        'DEFAULT TABLESPACE ' || USER_REC.TABLESPACE_NAME;
      -- 输出，方便查看创建结果
      DBMS_OUTPUT.PUT_LINE('用户 ' || USER_REC.USER_NAME || ' 创建成功。');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('用户 ' || USER_REC.USER_NAME || ' 创建失败。错误信息：' || SQLERRM);
    END;
  END LOOP;

DBMS_OUTPUT.PUT_LINE('用户创建：结束');

  -- 设置返回值
  O_RETURN_CODE := 1;
  O_RETURN_MSG := '执行成功';

EXCEPTION
  WHEN OTHERS THEN
    O_RETURN_CODE := SQLCODE;
    O_RETURN_MSG  := SQLERRM;
    -- ROLLBACK;
END;

5.授权创建表空间

1

grant create tablespace to TEST;

6.执行 SP 即可

实现方式二

其实也不用写存储过程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 表空间语句
SELECT 'CREATE TABLESPACE ' || A.TABLESPACE_NAME || ' DATAFILE ''' ||
       '/home/dmdba/data/testdb/' || A.TABLESPACE_NAME || '.DBF''' ||
       ' SIZE 500M AUTOEXTEND ON NEXT 40 MAXSIZE UNLIMITED;'
FROM TEST.T_TABLESPACE_USER A;

-- 用户语句
SELECT 'CREATE USER ' || A.USER_NAME || 
	     ' IDENTIFIED BY "123456789" DEFAULT TABLESPACE ' || 
	     A.TABLESPACE_NAME || ';'
FROM TEST.T_TABLESPACE_USER A;