Oracle使用外部表批量创建用户

整体思路：通过使用外部表将用户名导入Oracle的表中，然后通过PL/SQL遍历数据表，批量创建用户。

具体步骤如下：

1、在安装数据库的服务器的C盘根目录创建一个User List.txt文件，内容如下：

2、以sys用户登录数据库，创建directory对象。

create directory test_d as 'c:\';

3、给scott用户赋予读写directory对象的权限；

grant read, write on directory test_d to scott;

4、切换到scott用户，创建外部表，并关联到c盘根目录下的User List.txt文件

SQL> show user;
User is "scott"

create table t_ex_user
(
uname VARCHAR2(100)
)
organization external
(
type oracle_loader
default directory test_d
location ('User List.txt')
)
reject limit unlimited;

5、检查一下t_ex_user表中是否有内容

SQL> select * from t_ex_user;

UNAME
--------------------------------------------------------------------------------
user1
user2
user3
user4

6、切换回sys用户批量建立用户

begin
for us in (select uname from scott.t_ex_user) loop
dbms_output.put_line(us.uname);
execute immediate 'create user ' || us.uname || ' identified by ' ||
us.uname;
end loop;
end;
/

7、查看用户是否成功创建

SQL> select * from dba_users where username like '%USER%';

USERNAME                          USER_ID PASSWORD                       ACCOUNT_STATUS                   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     PROFILE   ...                     
------------------------------ ---------- ------------------------------ -------------------------------- ----------- ----------- ------------------------------ ------------------------------ ----------- ----------------------
USER3                                 102                                OPEN                                         2018-02-26  USERS                          TEMP                           2017-08-30  DEFAULT   ...                    
USER1                                 100                                OPEN                                         2018-02-26  USERS                          TEMP                           2017-08-30  DEFAULT   ...                   
USER4                                 103                                OPEN                                         2018-02-26  USERS                          TEMP                           2017-08-30  DEFAULT   ...                 
USER2                                 101                                OPEN                                         2018-02-26  USERS                          TEMP                           2017-08-30  DEFAULT   ...                    
　　

注意事项：

1、本文用的测试文件在安装数据库的服务器上，开始我把文件放到了PL/SQL Developer所在的机器上，报以下错误。是否可以用远程文件需要再研究。

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误打开文件 d:\T_EX_USER_1240_2936.log 时出错
ORA-06512: 在 line 2

2、文件应该支持txt、csv，开始用xlsx不行。。。

3、其实用UltraEdit的列编辑整理一下最方便。。。

 

转载于:https://www.cnblogs.com/xidabei/p/7453274.html