user_creation_script.sql

-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Get the DDL for the database USER creation. Here, I am excluding the Oracle related schemas.
-- Call Syntax  : @user_creation_script.sql
-- Last Modified: 17/03/2015
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET LONG 9999999 longchunksize 9999999 pagesize 0 linesize 300 feedback OFF verify OFF trimspool ON head OFF echo OFF
SELECT dbms_metadata.get_ddl('USER', u.username)
  || ';'
FROM dba_users u
WHERE u.username NOT IN ('APPQOSSYS','DBSNMP','DIP','ORACLE_OCM','OUTLN','SYS','SYSTEM')
UNION ALL
SELECT dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username)
  ||' /'
FROM dba_ts_quotas tq
WHERE tq.username NOT IN ('APPQOSSYS','DBSNMP','DIP','ORACLE_OCM','OUTLN','SYS','SYSTEM');
spool user_creation_ddls.sql
/
spool OFF;
SET linesize 80 pagesize 14 feedback ON trimspool ON verify ON