www.eygle.com

SET verify off;
SET termout off;
SET feedback off;
SET echo off;
SET pagesize 0;
SET timeing off;

SET termout on
SELECT 'Creating user build script...' FROM DUAL;
SET termout off;

CREATE TABLE usr_temp( lineno NUMBER,usr_name VARCHAR2(30),text VARCHAR2(80))
/

DECLARE
CURSOR usr_cursor
IS
SELECT username, PASSWORD, default_tablespace, temporary_tablespace,
PROFILE
FROM SYS.dba_users
WHERE username != 'SYS' AND username != 'SYSTEM'
ORDER BY username;

CURSOR qta_cursor (c_usr VARCHAR2)
IS
SELECT tablespace_name, max_bytes
FROM SYS.dba_ts_quotas
WHERE username = c_usr;

lv_username SYS.dba_users.username%TYPE;
lv_password SYS.dba_users.PASSWORD%TYPE;
lv_default_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_temporary_tablespace SYS.dba_users.default_tablespace%TYPE;
lv_profile SYS.dba_users.PROFILE%TYPE;
lv_tablespace_name SYS.dba_ts_quotas.tablespace_name%TYPE;
lv_max_bytes SYS.dba_ts_quotas.max_bytes%TYPE;
lv_string VARCHAR2 (80);
lv_lineno NUMBER := 0;

PROCEDURE write_out (p_line INTEGER, p_name VARCHAR2, p_string VARCHAR2)
IS
BEGIN
INSERT INTO usr_temp
(lineno, usr_name, text
)
VALUES (p_line, p_name, p_string
);
END;
BEGIN
OPEN usr_cursor;

LOOP
FETCH usr_cursor
INTO lv_username, lv_password, lv_default_tablespace,
lv_temporary_tablespace, lv_profile;

EXIT WHEN usr_cursor%NOTFOUND;
lv_lineno := 1;
lv_string := ('CREATE USER ' || LOWER (lv_username));
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;

IF lv_password IS NULL
THEN
lv_string := 'IDENTIFIED EXTERNALLY';
ELSE
lv_string := ('IDENTIFIED BY VALUES ''' || lv_password || '''');
END IF;

write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'DEFAULT TABLESPACE ' || lv_default_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TEMPORARY TABLESPACE ' || lv_temporary_tablespace;
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;

OPEN qta_cursor (lv_username);

LOOP
FETCH qta_cursor
INTO lv_tablespace_name, lv_max_bytes;

EXIT WHEN qta_cursor%NOTFOUND;
lv_lineno := lv_lineno + 1;

IF lv_max_bytes IS NULL
THEN
lv_string := 'QUOTA UNLIMITED ON ' || lv_tablespace_name;
ELSE
lv_string :=
'QUOTA ' || lv_max_bytes || ' ON ' || lv_tablespace_name;
END IF;

write_out (lv_lineno, lv_username, lv_string);
END LOOP;

CLOSE qta_cursor;

lv_string := ('PROFILE ' || lv_profile || ';');
write_out (lv_lineno, lv_username, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := ' ';
write_out (lv_lineno, lv_username, lv_string);
END LOOP;

CLOSE usr_cursor;
END;
/

SPOOL create_users.sql
SET heading off
SET recsep off
COL test format a80 word_wrap

SELECT text
FROM usr_temp
ORDER BY usr_name, lineno;

SPOOL off;

DROP TABLE usr_temp;
EXIT

运行该脚本后会产生一个create_users.sql的输出文件,输出内容参考范例如下: