rem $DBA/crusers.sql
rem
rem This script generates another script that will include all the CREATE USER
rem statements for those in the database (except for the SYS user).
rem
rem Note:  Don't forget the user grants and synonyms.
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem Created 10/21/96 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
define q=chr(39)
select 'Creating user build script...' from dual;

create table u_temp (	lineno NUMBER,
			text VARCHAR2(80));

declare
	cursor user_cursor is select
		username,
		password,
		default_tablespace,
		temporary_tablespace,
		profile
		from sys.dba_users
		where username != 'SYS'
		order by username;

	cursor quo_cursor (c_user varchar2) is select
		tablespace_name, max_bytes
		from sys.dba_ts_quotas
		where username = c_user;

	lv_username		sys.dba_users.username%TYPE;
	lv_password		sys.dba_users.password%TYPE;
	lv_default_tbl		sys.dba_users.default_tablespace%TYPE;
	lv_temp_tbl		sys.dba_users.temporary_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;

	procedure write_out is
	begin
		lv_lineno := lv_lineno + 1;
		insert into u_temp (lineno, text) values (lv_lineno, lv_string);
	end;

begin
	lv_lineno := 0;
	open user_cursor;
	loop
		fetch user_cursor into
			lv_username,
			lv_password,
			lv_default_tbl,
			lv_temp_tbl,
			lv_profile;
		exit when user_cursor%NOTFOUND;
		lv_string := 'CREATE USER '||lv_username||
			' identified by values '||&q||lv_password||&q;
		write_out;
		lv_string := '    default tablespace '||lv_default_tbl;
		write_out;
		lv_string := '    temporary tablespace '||lv_temp_tbl;
		write_out;
		open quo_cursor(lv_username);
		loop
			fetch quo_cursor into lv_tablespace_name, lv_max_bytes;
			exit when quo_cursor%NOTFOUND;
			if lv_max_bytes = -1 then
				lv_string := '    quota unlimited on '||
					lv_tablespace_name;
			elsif mod(lv_max_bytes, 1048576) = 0 then
				lv_string := '    quota '||
					lv_max_bytes/1048576||
					'M on '||lv_tablespace_name;
			elsif mod(lv_max_bytes, 1024) = 0 then
				lv_string := '    quota '||lv_max_bytes/1024||
					'K on '||lv_tablespace_name;
			else
				lv_string := '    quota '||lv_max_bytes||
					' on '||lv_tablespace_name;
			end if;
			write_out;
		end loop;
		close quo_cursor;
		lv_string := '    profile '||lv_profile||';';
		write_out;
	end loop;
	close user_cursor;
end;
/

set termout off
set heading off
spool cr_user.sql
select 'rem cr_user.sql' from dual;
select 'rem' from dual;
select 'rem ***** All users for database ' || name from v$database;
select 'rem' from dual;
select 'set feedback off' from dual;
select text from u_temp order by lineno;
spool off
drop table u_temp;
set termout on
select 'Created cr_user.sql...' from dual;
set termout off
exit