-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- 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