Generate a script to create database users



My OraFAQ Blog

Contact me

This is a really full-featured user creation script. It generates a CREATE USER script for all non-system database users, including:

Common "special" database users, ie. system users plus default users that will already be present in many pre-created databases, are excluded. eg, SYS, SYSTEM, DBSNMP (Jobs/SNMP account), OUTLN, CTXSYS (Oracle Text schema), HR/OE/PM/SH (demo schemas), etc. If you don't want to exclude all of these, comment out the "where" clause.

set pagesize 0
set escape on
select 'create user ' || U.username || ' identified ' || 
      ' by values ' || '''' || password || ''''
|| chr(10) ||
'default tablespace ' || default_tablespace || chr(10) ||
'temporary tablespace ' || temporary_Tablespace || chr(10) ||
' profile ' || profile || chr(10) ||
'quota ' ||
decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes) ||
' on ' || default_tablespace || 
decode (account_status,'LOCKED', ' account lock',
			'EXPIRED', ' password expire',
			'EXPIRED \& LOCKED', ' account lock password expire',
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
set pagesize 100
set escape off

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...