源代码如下：
CREATE TABLE CREATEUSERSCRIPT
(
SEQ NUMBER,
USERNAME VARCHAR2(50 BYTE),
STR_SQL VARCHAR2(4000 BYTE)
)
CREATE OR REPLACE PROCEDURE p_generate_user
IS
CURSOR c
IS
SELECT u.username,
DECODE (PASSWORD,
NULL, 'EXTERNALLY',
' by values ' || '''' || PASSWORD || ''''
) passwd,
default_tablespace, temporary_tablespace, PROFILE,
DECODE (account_status,
'OPEN', ' account unlock',
'EXPIRED & LOCKED', ' account lock password expire',
NULL
) account_status
FROM dba_users u, dba_ts_quotas q
-- Comment this clause out to include system & default users
WHERE u.username NOT IN
('SCOTT',
'DBSNMP',
'OUTLN',
'WKPROXY',
'ORDPLUGINS',
'CTXSYS',
'XDB',
'ANONYMOUS',
'OWNER',
'ODM_MTR',
'ODM',
'HR',
'OE',
'PM',
'SH',
'QS_ADM',
'QS',
'QS_WS',
'QS_ES',
'QS_OS',
'QS_CBADM',
'QS_CB',
'QS_CS',
'PERFSTAT',
'SYSTEM',
'SYS',
'SYSMAN',
'WKSYS',
'OLAPSYS',
'WMSYS',
'DMSYS',
'EXFSYS',
'CTXSYS',
'ORDSYS',
'MDSYS'
)
AND u.username = q.username(+)
AND u.default_tablespace = q.tablespace_name(+);
r c%ROWTYPE;
v_sql VARCHAR2 (4000);
v_default_role VARCHAR2 (4000);
v_sys_privilege VARCHAR2 (4000);
i INT := 0;
v_username createuserscript.username%TYPE := ' ';
BEGIN
EXECUTE IMMEDIATE 'truncate table createuserscript';
OPEN c;
LOOP
FETCH c
INTO r;
EXIT WHEN c%NOTFOUND;
-- 建立用户基本信息
v_sql :=
'create user '
|| r.username
|| CHR (10)
|| ' identified '
|| r.passwd
|| CHR (10)
|| ' default tablespace '
|| r.default_tablespace
|| CHR (10)
|| ' temporary tablespace '
|| r.temporary_tablespace
|| CHR (10)
|| ' profile '
|| r.PROFILE
|| CHR (10)
|| r.account_status
|| ';';
v_default_role := '';
i := i + 1;
INSERT INTO createuserscript
VALUES (i, r.username, v_sql);
-- 授予角色
FOR r1 IN (SELECT *
FROM dba_role_privs
WHERE grantee = r.username)
LOOP
v_sql := 'GRANT ' || r1.granted_role || ' TO ' || r1.grantee;
IF r1.admin_option = 'YES'
THEN
v_sql := v_sql || ' WITH ADMIN OPTION;';
ELSE
v_sql := v_sql || ' ;';
END IF;
IF r1.default_role = 'YES'
THEN
v_default_role := v_default_role || ' ' || r1.granted_role || ',';
END IF;
i := i + 1;
INSERT INTO createuserscript
VALUES (i, r.username, v_sql);
END LOOP;
IF v_default_role IS NULL
THEN
v_sql := 'ALTER USER ' || r.username || ' DEFAULT ROLE none;';
ELSE
v_sql :=
'ALTER USER '
|| r.username
|| ' DEFAULT ROLE '
|| RTRIM (v_default_role, ',')
|| ';';
END IF;
i := i + 1;
INSERT INTO createuserscript
VALUES (i, r.username, v_sql);
-- 系统权限
v_sys_privilege := '';
FOR r1 IN (SELECT *
FROM dba_sys_privs
WHERE grantee = r.username)
LOOP
v_sql := 'GRANT ' || r1.PRIVILEGE || ' TO ' || r1.grantee;
IF r1.admin_option = 'YES'
THEN
v_sql := v_sql || ' WITH ADMIN OPTION;';
ELSE
v_sql := v_sql || ' ;';
END IF;
i := i + 1;
INSERT INTO createuserscript
VALUES (i, r.username, v_sql);
END LOOP;
-- 对象权限
v_sys_privilege := '';
FOR r1 IN (SELECT *
FROM dba_tab_privs
WHERE grantee = r.username)
LOOP
v_sql :=
'GRANT '
|| r1.PRIVILEGE
|| ' on '
|| r1.owner
|| '.'
|| r1.table_name
|| ' TO '
|| r1.grantee;
IF r1.grantable = 'YES'
THEN
v_sql := v_sql || ' WITH GRANT OPTION;';
ELSE
v_sql := v_sql || ' ;';
END IF;
i := i + 1;
INSERT INTO createuserscript
VALUES (i, r.username, v_sql);
END LOOP;
IF v_username != r.username
THEN
i := i + 1;
INSERT INTO createuserscript
VALUES (i, NULL, NULL);
i := i + 1;
INSERT INTO createuserscript
VALUES (i, NULL, NULL);
END IF;
v_username := r.username;
END LOOP;
CLOSE c;
COMMIT;
END p_generate_user;
/
# createuser.sql
set echo off
set feedback off
set verify off
set pagesize 0
set linesize 100
begin
p_generate_user;
end;
/
-- 生成脚本存储到 create_user.sql 文件中
spool /home/oracle/dbbak/create_user.sql
select str_sql from CREATEUSERSCRIPT order by seq;
spool off
set echo on
set feedback on
set verify on
1. 建立存储结果的表
CREATE TABLE CREATEUSERSCRIPT( SEQ NUMBER, USERNAME VARCHAR2(50 BYTE), STR_SQL VARCHAR2(4000 BYTE))
2. 生成 SQL 语句，并插入上表中
CREATE OR REPLACE PROCEDURE p_generate_userIS CURSOR c IS SELECT u.username, DECODE (PASSWORD, NULL, 'EXTERNALLY', ' by values ' || '''' || PASSWORD || '''' ) passwd, default_tablespace, temporary_tablespace, PROFILE, DECODE (account_status, 'OPEN', ' account unlock', 'EXPIRED & LOCKED', ' account lock password expire', NULL ) account_status FROM dba_users u, dba_ts_quotas q-- Comment this clause out to include system & default users WHERE u.username NOT IN ('SCOTT', 'DBSNMP', 'OUTLN', 'WKPROXY', 'ORDPLUGINS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'OWNER', 'ODM_MTR', 'ODM', 'HR', 'OE', 'PM', 'SH', 'QS_ADM', 'QS', 'QS_WS', 'QS_ES', 'QS_OS', 'QS_CBADM', 'QS_CB', 'QS_CS', 'PERFSTAT', 'SYSTEM', 'SYS', 'SYSMAN', 'WKSYS', 'OLAPSYS', 'WMSYS', 'DMSYS', 'EXFSYS', 'CTXSYS', 'ORDSYS', 'MDSYS' ) AND u.username = q.username(+) AND u.default_tablespace = q.tablespace_name(+); r c%ROWTYPE; v_sql VARCHAR2 (4000); v_default_role VARCHAR2 (4000); v_sys_privilege VARCHAR2 (4000); i INT := 0; v_username createuserscript.username%TYPE := ' ';BEGIN EXECUTE IMMEDIATE 'truncate table createuserscript'; OPEN c; LOOP FETCH c INTO r; EXIT WHEN c%NOTFOUND; -- 建立用户基本信息 v_sql := 'create user ' || r.username || CHR (10) || ' identified ' || r.passwd || CHR (10) || ' default tablespace ' || r.default_tablespace || CHR (10) || ' temporary tablespace ' || r.temporary_tablespace || CHR (10) || ' profile ' || r.PROFILE || CHR (10) || r.account_status || ';'; v_default_role := ''; i := i + 1; INSERT INTO createuserscript VALUES (i, r.username, v_sql); -- 授予角色 FOR r1 IN (SELECT * FROM dba_role_privs WHERE grantee = r.username) LOOP v_sql := 'GRANT ' || r1.granted_role || ' TO ' || r1.grantee; IF r1.admin_option = 'YES' THEN v_sql := v_sql || ' WITH ADMIN OPTION;'; ELSE v_sql := v_sql || ' ;'; END IF; IF r1.default_role = 'YES' THEN v_default_role := v_default_role || ' ' || r1.granted_role || ','; END IF; i := i + 1; INSERT INTO createuserscript VALUES (i, r.username, v_sql); END LOOP; IF v_default_role IS NULL THEN v_sql := 'ALTER USER ' || r.username || ' DEFAULT ROLE none;'; ELSE v_sql := 'ALTER USER ' || r.username || ' DEFAULT ROLE ' || RTRIM (v_default_role, ',') || ';'; END IF; i := i + 1; INSERT INTO createuserscript VALUES (i, r.username, v_sql); -- 系统权限 v_sys_privilege := ''; FOR r1 IN (SELECT * FROM dba_sys_privs WHERE grantee = r.username) LOOP v_sql := 'GRANT ' || r1.PRIVILEGE || ' TO ' || r1.grantee; IF r1.admin_option = 'YES' THEN v_sql := v_sql || ' WITH ADMIN OPTION;'; ELSE v_sql := v_sql || ' ;'; END IF; i := i + 1; INSERT INTO createuserscript VALUES (i, r.username, v_sql); END LOOP; -- 对象权限 v_sys_privilege := ''; FOR r1 IN (SELECT * FROM dba_tab_privs WHERE grantee = r.username) LOOP v_sql := 'GRANT ' || r1.PRIVILEGE || ' on ' || r1.owner || '.' || r1.table_name || ' TO ' || r1.grantee; IF r1.grantable = 'YES' THEN v_sql := v_sql || ' WITH GRANT OPTION;'; ELSE v_sql := v_sql || ' ;'; END IF; i := i + 1; INSERT INTO createuserscript VALUES (i, r.username, v_sql); END LOOP; IF v_username != r.username THEN i := i + 1; INSERT INTO createuserscript VALUES (i, NULL, NULL); i := i + 1; INSERT INTO createuserscript VALUES (i, NULL, NULL); END IF; v_username := r.username; END LOOP; CLOSE c; COMMIT;END p_generate_user;/
3. 生成调用过程的 .sql 文件
# createuser.sqlset echo offset feedback offset verify offset pagesize 0set linesize 100beginp_generate_user;end;/-- 生成脚本存储到 create_user.sql 文件中spool /home/oracle/dbbak/create_user.sqlselect str_sql from CREATEUSERSCRIPT order by seq;spool offset echo onset feedback onset verify on